Cells property question Run-time error 1004

[dpaul2]

Hi

Trying to understand Cells property better. When I use Cells property below for Sheet1 it works, but when I try to then use Cells property against SHEET2 I get Runtime error 1004.
It seems that Cells property is tied to Sheet1 somehow. Can someone explain what is happening under the covers. Thanks!!!

Dim varSheetA As Variant
Dim varSheetB As Variant
varSheetA = Worksheets("Sheet1").Range(Cells(1, 1), Cells(16, 7)) - works no issue
varSheetB = Worksheets("Sheet2").Range(Cells(1, 1), Cells(16, 7)) -Run-time error 1004


varSheetB = Worksheets("Sheet2").Range("A1:G16") - works no issue

 

[Fluff]

You have not qualified the cells, so they are looking at the active sheet.
It needs to be like

varSheetB = Worksheets("Sheet2").Range(Worksheets("Sheet2").Cells(1, 1), Worksheets("Sheet2").Cells(16, 7))

Or simpler

With Worksheets("Sheet2")
   varSheetB = .Range(.Cells(1, 1), .Cells(16, 7))
End With

 

[dpaul2]

Thanks much Fluff!

 

[Fluff]

You're welcome & thanks for the feedback