CODEWITHSUNDEEP
vbaexcel
Sartorialist
Sartorialist

Reputation: 301

Excel VBA: Copying/Pasting Range

I'm writing a macro that opens a number of different workbooks, copies data from each, and compiles into a single "master" workbook. In the below code example, wb2 is one of the workbooks and I'm copying from, and wb1 is the master.

lrow3A is the last row of data in the source workbook. Lrow3 is the last row of data in the master workbook.

lrow3A = wb2.Sheets("DCF3").Cells(1048576, 2).End(xlUp).Row
wb2.Sheets("DCF3").Range(Cells(6, 1), Cells(lrow3A, 16)).Copy _
    Destination:=wb2.Worksheets("DCF3").Cells(lrow3 + 1, 2)

I'm getting a "Subscript out of range" error on the copy line.

Upvotes: 0

Views: 912

Answers (1)

DisplayName
DisplayName

Reputation: 13386

I think you should code:

With wb2.Sheets("DCF3") 'reference "source" worksheet
    lrow3A = .Cells(.Rows.Count, 2).End(xlUp).Row ' get referenced sheet column "B" last not empty cell row index 
    .Range("A6:P" & lrow3A).Copy _
    Destination:=wb1.Worksheets("DCF3").Cells(lrow3 + 1, 2) 'copy referenced sheet range in columns A:P from row 6 to row 'lrow3A' and paste it to "master" workbook sheet "DCF3" starting from its column B cell at row 'lrow3'+1
End With

Upvotes: 2

Related Questions