23

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?

user2597163
  • 249
  • 1
  • 2
  • 4

1 Answers1

48

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
Dick Kusleika
  • 32,673
  • 4
  • 52
  • 73
  • 1
    no need to qualify the range in the instruction `shDest.Range(shDest.Cells(1, 1), shDest.Cells(2, 2)).Value = _ shSource.Range(shSource.Cells(1, 1), shSource.Cells(2, 2)).Value ` this is also correct `Range(shDest.Cells(1, 1), shDest.Cells(2, 2)).Value= _ Range(shSource.Cells(1, 1), shSource.Cells(2, 2)).Value` – h2so4 Apr 22 '17 at 10:23
  • 1
    @h2so4 - Wait - so does that mean you don't have to qualify the `Range()` if you do the `Cells()` inside? Is that explicitly correct, or does it "just work"...I've always qualified all parts of a `Range(cells(),Cells())` and it'd be neat if I can skip the `Range()` qualifier ... – BruceWayne Jan 22 '20 at 16:23
  • 1
    @BruceWayne, Excel documentation that I have found so far is not clear about this. (I have found that prefixing the range is a must in a sheet module, but not needed in any other type of modules. Is this explicitly correct or does it just work by chance, I don't know ... – h2so4 Jan 23 '20 at 18:41
  • 2
    My guess (and it's only a guess) is that it's explicitly correct. Since Range can take a variery of parameter types - a string that evaluates to a range, a single cell object, two cell objects - that there is stuff going on behind the scenes to evaluate what kind of parameter you're sending. If you send a fully qualified cell object, I'll bet that it skips most of the evaluating/converting code and it's the straightest path through the code. Like it doesn't convert it to a string and then back to a cell. So for my money, you can omit the reference from Range if Cells are qualified. – Dick Kusleika Jan 23 '20 at 19:40
  • 1
    And I will still probably type it because I'm a creature of habit. – Dick Kusleika Jan 23 '20 at 19:40
  • 2
    While leaving Range unqualified does work, qualifying it too it slightly faster. – chris neilsen Mar 27 '20 at 21:28
  • A thought on error proofing: if one habitually uses the @Dick Kusleika 's sytnax of a "with" block and dot-qualified .range and .cell , does it not force the programmer away from mis-directed references, and force the later reader/debugger to understand the intent (even if it's all the original programmer...... ! ) – John Apr 29 '22 at 20:04