Run-time error '1004' while copying

Question

I'm getting the infamous Run-time error when I run this code, and I don't understand why:

With ThisWorkbook.Sheets("Data") .Range(Cells(row1, column1), Cells(row2, column1)).Copy End With

row1, column1 and row2 are all defined as Integers.

The error pops up for the second line of code.

Can I get some insight please?

AnalystCave.com · Accepted Answer · 2015-07-06T17:26:17.890

1

You forgot the "." before Cells (as you are within the With scope of the Sheet Data)

With ThisWorkbook.Sheets("Sheet2")
    .Range(.Cells(1, 1), .Cells(2, 2)).Copy
End With

Tested the above example and now it works for me.

edited Jul 06 '15 at 17:26

answered Jul 06 '15 at 17:24

AnalystCave.com

Good catch. I dealt with a very similar situation [here](http://stackoverflow.com/questions/27763089/count-the-number-of-rows-in-another-sheet/27763394#27763394). – Jul 06 '15 at 17:26
Thanks :). Quite a common error of my own actually. Too bad Excel does not generate more understandable error messages.... – AnalystCave.com Jul 06 '15 at 17:27
Thanks, worked like a charm! It's the first time I use the "With...End with", so I wasn't too sure of the syntax. – Jean-Pierre Cormier Jul 06 '15 at 17:27
Great to hear that. Please mark as answer to close thread then – AnalystCave.com Jul 06 '15 at 17:27

score 0 · Answer 2 · answered Jul 06 '15 at 17:31

0

Where are you coping the range to. The proper format is

source.copy destination

Sheets("Count").Range("C2:D3").Copy Sheets("Count").Range("E2:F3")

answered Jul 06 '15 at 17:31

MatthewD

2 Answers2