I'm trying to move some data from one workbook into another by assigning the values from one range to another. When I use the normal Range syntax to specify the destination range (Range("A1:B2")) my code works, but if I try to use the Range, Cells syntax (Range(Cells(1,1),Cells(2,2))) my code doesn't work.
I activate the destination workbook (ActiveWorkbook) and have the code running in the source workbook (ThisWorkbook).
This code works:
ActiveWorkbook.Worksheets(1).Range("A1:B2").Value _
= ThisWorkbook.Worksheets(1).Range(Cells(1, 1), Cells(2, 2)).Value
But This code does not:
ActiveWorkbook.Worksheets(1).Range(Cells(1, 1), Cells(2, 2)).Value _
= ThisWorkbook.Worksheets(1).Range(Cells(1, 1), Cells(2, 2)).Value
The error I get is Run-time error '1004': Applicaton-defined or object-defined error.
Does anyone know why using the cells object is causing me problems, or if there is some other problem I'm not aware of?
Best Answer
The problem is that
Cells
is unqualified, which means that the sheet to which those cells refer is different depending on where your code is. Any time you callRange
orCells
orRows
orUsedRange
or anything that returns a Range object, and you don't specify which sheet it's on, the sheet gets assigned according to:You qualify the
Range
reference, but theCells
reference is unqualified and is likely pointing to the Activesheet. It's like writingwhich of course doesn't make any sense unless ThisWorkbook.Worksheets(1) happens to be active. I often like to use a With block so that I make sure everything is fully qualified.
But you refer to two different sheets, so you'll be better off using short sheet variables like:
But really, if you're going to hardcode the
Cells
arguments, you could clean that up like