0

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.

BruceWayne
  • 22,923
  • 15
  • 65
  • 110
Sartorialist
  • 291
  • 2
  • 18
  • 4
    If you are copying to the master, should that not be Destination:=wb1 ? – Glib Feb 19 '18 at 20:37
  • 2
    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 Feb 19 '18 at 20:54
  • Your `.Range(Cells(6, 1), Cells(lrow3A, 16))` lacks proper parent worksheet referencing. See [this](https://stackoverflow.com/questions/36368220/is-the-in-range-necessary-when-defined-by-cells). –  Feb 19 '18 at 23:22

1 Answers1

2

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
DisplayName
  • 13,283
  • 2
  • 11
  • 19