Copy a range from one worksheet to another using cells property

Question

I am trying to copy a range from one worksheet to another. I have multiple ranges that I would like to copy and paste at various points in the new worksheet though (for example, I want to paste the first range to D3, then the next range to E12, and so on), so I am using the cells property to describe the range. However, for some reason, when I describe the range that I want to paste at in the new worksheet using the cells format rather than just putting in a string, it does not work.

This line of code does work:

Sheets("Sheet1").Range(Cells(2, 3), Cells(10, 3)).Copy _
  Destination:=Sheets("Sheet2").Range("D3")

but this one does not:

Sheets("Sheet1").Range(Cells(2, 3), Cells(10, 3)).Copy _
  Destination:=Sheets("Sheet2").Range(Cells(3,4))

When I run the latter line of code, I get a "Run-time error 1004: application-defined or object defined error". Why is this and how can I fix it?

Unknown Unknown · Accepted Answer · 2015-05-27T20:34:57

You are specifying the parent of Range but not of Cells. In short, you are trying to define a range of cells on Sheet1 that contain the cells on Sheet2 and/or vise-versa.

dim ws2 as worksheet
set ws2 = sheets("Sheet2")

with sheets("Sheet1")
    .Range(.Cells(2, 3), .Cells(10, 3)).Copy _
      Destination:=ws2.Range(ws2.Cells(3,4))
end with

The prefixing period (aka . or full stop) means that the parent of .Range and .Cells is defined with the With...End With statement. To shorten the code necessary to properly define the range on Sheet2, I assigned a worksheet type variable and used it to show the parent of both the range and the cells.

FWIW, ws2.Range(ws2.Cells(3,4)) is redundant. ws2.Cells(3,4) is sufficient.

Copy a range from one worksheet to another using cells property

1 Answers