Excel VBA: Copying/Pasting Range

Question

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.

If you are copying to the master, should that not be Destination:=wb1 ? — Glib
You need to add the workbook/sheets for the Cells() too. I think you need: wb2.Sheets("DCF3").Range(wb2.Sheets("DCF3").Cells(6, 1), wb2.Sheets("DCF3").Cells(lrow3A, 16)).Copy — BruceWayne
Your .Range(Cells(6, 1), Cells(lrow3A, 16)) lacks proper parent worksheet referencing. See this. — user4039065

DisplayName DisplayName · Accepted Answer · 2018-02-19T21:21:07

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

Excel VBA: Copying/Pasting Range

1 Answers