Why does Range work, but not Cells?

by

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

Leave a Comment

tech