Why does Range work, but not Cells?

Question

I am trying to move some data from one workbook to another by assigning values ​​from one range to another. When I use the standard Range syntax to determine the destination range (Range ("A1: B2"), my code works, but if I try to use the Range syntax, Cells (Range (Cells (1,1), Cells (2), 2) )) my code does not work.

I activate the destination book (ActiveWorkbook) and execute the code in the source book (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 the "1004" runtime error: user-defined or user-defined error.

Does anyone know why using the cell object is causing me problems, or if there is some other problem that I don't know about?

Answer 1

The problem is that Cells is unqualified, which means that the sheet to which these cells belong differs depending on where your code is located. Each time you call Range or Cells or Rows or UsedRange or something that returns a Range object, and you don’t indicate which sheet it is on, the sheet gets assigned according to:

• In sheet class module: this sheet no matter how active
• In any other module: ActiveSheet

You qualify the Range link, but the Cells link is unqualified and probably points to the Active List. This is how to write

 ThisWorkbook.Worksheets(1).Range(ActiveSheet.Cells(1, 1), ActiveSheetCells(2, 2)).Value 

which, of course, makes no sense unless ThisWorkbook.Worksheets (1) is active. I often like to use block “C”, so I make sure that everything is fully compliant.

 With Sheets(1) .Range(.Cells(1,1), .Cells(2,2)).Value = "something" End With 

But you are referencing two different sheets, so you better use short sheet variables, for example:

 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 are going to hard decode Cells arguments, you can clear them, for example

 shDest.Cells(1, 1).Resize(2, 2).Value = shSource.Cells(1, 1).Resize(2, 2).Value