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
– Glib, Commented Feb 19, 2018 at 20:37
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
– BruceWayne, Commented Feb 19, 2018 at 20:54
Your .Range(Cells(6, 1), Cells(lrow3A, 16)) lacks proper parent worksheet referencing. See this. — user4039065
– user4039065, Commented Feb 19, 2018 at 23:22

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

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

answered Feb 19, 2018 at 21:21

DisplayName

13.4k2 gold badges13 silver badges19 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

ashleych Over a year ago

How does one paste by values in this ?

DisplayName Over a year ago

@ashleych, check this out

Collectives™ on Stack Overflow

Excel VBA: Copying/Pasting Range

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related