Visual Basic use of EntireRow.Copy starting from a specific column

Electra 0
Viewed 18

I would like to copy entire rows from mutliple sheets to a target sheet. But I would like to start the row copy from column B of the target sheet. I tried

Sheet1.Range("13:32").EntireRow.Copy Sheet5.Range("2:21")

that works fine but starts from column A of target sheet, sheet5 ; I then tried

Sheet1.Range("13:32").EntireRow.Copy Sheet5.Range("B2:21")

but gives an error. Can you please help?

Thanks a lot, Electra

excel-formula excel
1 Answers

EntireRow will always return ... well ... the entire row! Therefore you receive an error on the second row - as there are too many cells to be inserted.

You have to resize the range using this function - it will reduce the range by one column and then offsets it to start at the second column.

Public Function resizeRowToStartFromColumnB(rg As Range) As Range

Dim rgResized As Range

With rg
    Set rgResized = .Resize(, .Columns.Count - 1).Offset(, 1)
End With

Set resizeRowToStartFromColumnB = rgResized
    
End Function

Within your code you could use it like:

resizeRowToStartFromColumnB(Sheet1.Range("13:32")).Copy Sheet5.Range("B2")
useryPy710
Related