When sheet isn't selected I get an error when I try to use ranges

[craig.penny]

I'm trying to make my code run faster by avoiding selecting anything. It works to a point but then I keep running into a problem when I try to access a range on a sheet that wasn't selected. I wrote a short example to understand the problem better.

Sub error1004()
 
Dim rng As Range
Dim shtName As String
 
shtName = "SheetName"
Set rng = Sheets(shtName).Range(Cells(2, 2), Cells(3, 3)) 'error 1004
rng.Copy
 
End Sub

I put this code in the "ThisWorkbook" and it works fine if sheet "SheetName" is selected but if a different sheet is selected it gives me error #1004. I feel like this is something really obvious that I'm just overlooking. Any help is greatly appreciated. Thanks.

 

[RoryA]

You need to qualify the Cells properties as well as the Range property with the worksheet object:

Sub error1004()
 
Dim rng As Range
Dim shtName As String
 
shtName = "SheetName"
Set rng = Sheets(shtName).Range(Sheets(shtName).Cells(2, 2), Sheets(shtName).Cells(3, 3)) 'error 1004
rng.Copy
 
End Sub

or you can neaten that up with a With...End With statement

Sub error1004()
 
Dim rng As Range
Dim shtName As String
 
shtName = "SheetName"
with Sheets(shtName)
   Set rng = .Range(.Cells(2, 2), .Cells(3, 3))
end with
rng.Copy
 
End Sub

 

[craig.penny]

Thanks!