Copy Destination Using Cell Integers in Range

[vthokienj]

I am trying to paste data from one worksheet to another and am not sure as to why it only works when using a standard range.

works:

Sheets("sheet1").Range("B4:Z10").Copy Destination:=Sheets("sheet2").Range("A1")

doesn't work (run time error 1004):

Sheets("sheet1").Range(Cells(4, 2), Cells(10, 26)).Copy Destination:=Sheets("sheet2").Range("A1")

These statements should be similar, or so I thought. I would like to be able to execute this copy using the second form.

Thanks

 

[Andrew Poulsom]

You need to qualify the Cells property as well as the Range property, otherwise the active sheet will be used:

With Sheets("sheet1")
    .Range(.Cells(4, 2), .Cells(10, 26)).Copy Destination:=Sheets("sheet2").Range("A1")
End With