Why does Range work, but not Cells?
cells, range, vba
Solution
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 call `Range` or `Cells` or `Rows` or `UsedRange` or anything that returns a Range object, and you don't specify which sheet it's on, the sheet gets assigned according to:
- In a sheet's class module: that sheet regardless of what's active
- In any other module: the ActiveSheet
You qualify the `Range` reference, but the `Cells` reference is unqualified and is likely pointing to the Activesheet. It's like writing
ThisWorkbook.Worksheets(1).Range(ActiveSheet.Cells(1, 1), ActiveSheetCells(2, 2)).Value
which 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.
With Sheets(1)
.Range(.Cells(1,1), .Cells(2,2)).Value = "something"
End With
But you refer to two different sheets, so you'll be better off using short sheet variables like:
Dim shSource As Worksheet
Dim shDest As Worksheet
Set shSource = ThisWorkbook.Worksheets(1)
Set shDest = Workbooks("myBook").Worksheets(1)
shDest.Range(shDest.Cells(1, 1), shDest.Cells(2, 2)).Value = _
shSource.Range(shSource.Cells(1, 1), shSource.Cells(2, 2)).Value
But really, if you're going to hardcode the `Cells` arguments, you could clean that up like
shDest.Cells(1, 1).Resize(2, 2).Value = shSource.Cells(1, 1).Resize(2, 2).Value
Problem
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?