Referencing a range that is not on the active sheet.

JeffreyWeir

Super Moderator
Staff member
Joined
Mar 22, 2011
Messages
357
Reaction score
0
Points
0
Location
New Zealand
I've got some code that says this:
Code:
Set TabOrderRange = Sheet6.Range("Controls", Cells(iTabOrderCount + 1, 3))
"Controls" is a one cell named range, that serves as a "Put the data here" handle.

This works fine if sheet6 is the active sheet, but fails otherwise with this error: Method 'Range' of object '_Worksheet' failed. Obviously I don't quite have a handle on how to qualify references. Can anyone tell me where I'm going wrong here?
 

Bob Phillips

Super Moderator
Staff member
Joined
Mar 21, 2011
Messages
1,940
Reaction score
0
Points
36
Excel Version(s)
O365
Try

Code:
Set TabOrderRange = Sheet6.Range("Controls", Sheet6.Cells(iTabOrderCount + 1, 3))
 

Bob Phillips

Super Moderator
Staff member
Joined
Mar 21, 2011
Messages
1,940
Reaction score
0
Points
36
Excel Version(s)
O365
Or even

Code:
With Sheet6

Set TabOrderRange = .Range(.Range("Controls"), .Cells(iTabOrderCount + 1, 3))
End With
 

JeffreyWeir

Super Moderator
Staff member
Joined
Mar 22, 2011
Messages
357
Reaction score
0
Points
0
Location
New Zealand
Perfect. Thanks Bob...it's like you're sitting right next to me. Why the heck should one need to reference the particular sheet the cells are on inside the RANGE arguement as well as outside of it? THere's so many little things like this to rote learn....profficiency in VBA is definately hard won.
 

Bob Phillips

Super Moderator
Staff member
Joined
Mar 21, 2011
Messages
1,940
Reaction score
0
Points
36
Excel Version(s)
O365
The first Sheet6.Range refers to an overall range, but the second is its own range object and by not qualifying it defaults to the activesheet, so you are trying to refer to a range on a specified sheet where part of the range is on another sheet, hence the error.
 
Top