Copy a range from one sheet to another using the cell property
I am trying to copy a range from one sheet to another. I have multiple ranges that I would like to copy and paste at different points in a new sheet (for example, I want to paste the first range in D3, then the next range to E12, etc.), so I use the cells property to describe the range ... However, for some reason, when I describe the range I want to insert in a new sheet using the cell format rather than just inserting a row, it doesn't work.
This line of code works:
Sheets("Sheet1").Range(Cells(2, 3), Cells(10, 3)).Copy _
Destination:=Sheets("Sheet2").Range("D3")
but this is not:
Sheets("Sheet1").Range(Cells(2, 3), Cells(10, 3)).Copy _
Destination:=Sheets("Sheet2").Range(Cells(3,4))
When I run the last line of code, I get "Runtime Error 1004: Application Defined Error or Object Error". Why is this and how can I fix it?
source to share
You are specifying the parent of the Range, but not the cells. In short, you are trying to define a range of cells on Sheet1 that contain cells on Sheet2 and / or vice 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 prefix period (aka .
or full stop) means the parent .Range
and .Cells
is defined with the With ... End With statement . To cut down on the code required to correctly define the range on Sheet2, I assigned a worksheet type variable and used it to display the parent of both the range and the cells.
FWIW ws2.Range(ws2.Cells(3,4))
is redundant. ws2.Cells(3,4)
...
source to share