Excel Macro Macro / VBA Error 1004 - Copy Paste With Variable

Question

Why does this work:

Sheets("Sheet1-Orig").Range(Sheets("Sheet1-Orig").Cells(20, 1), Sheets("Sheet1-Orig").Cells(LastRow1, 3)).Copy Destination:=Sheets("3-Orig_Merged").Range("A1")

But this throws up a 1004:

Sheets("Sheet2-Orig").Range(Sheets("Sheet1-Orig").Cells(2, 1), Sheets("Sheet1-Orig").Cells(LastRow2, 3)).Copy Sheets("3-Orig_Merged").Cells(LastRow1 - 18, 1)

Getting Application defined or object defined error. Driving. Me. Nuts...

Thanks!

The range and Cell object must be on the same sheet when using cells() inside Range(). — Scott Craner
– Scott Craner, Commented Sep 14, 2017 at 14:00

Shai Rado · Accepted Answer · 2017-09-14 14:26:48Z

1

Even though there are more code lines below, I find it easier to use (and debug later):

With Sheets("Sheet1-Orig")
    .Range(.Cells(20, 1), .Cells(LastRow1, 3)).Copy
End With
Sheets("3-Orig_Merged").Range("A1").PasteSpecial

answered Sep 14, 2017 at 14:26

Shai Rado

33.7k7 gold badges32 silver badges55 bronze badges

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

3 Comments

Paul Demay Over a year ago

The biggest difference between the line that works and the line that doesn't work is that I am pasting to a defined cell (A1) in the line that works, but I am trying to paste to a variable (LastRow1 - 18) in the one that gives the error. The LastRow1 variable has the correct row number (65018) in it. It seems like I can copy using a variable in the range, but I can't paste to one?

Scott Craner Over a year ago

@PaulDemay no the issue is that you are referring to two different sheets in the range you are trying to copy.

Paul Demay Over a year ago

OMG Scott - duh.

Collectives™ on Stack Overflow

Excel Macro Macro / VBA Error 1004 - Copy Paste With Variable

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related