Referencing a range that is not on the active sheet.

JeffreyWeir

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?
 
Try

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

Code: 
With Sheet6

Set TabOrderRange = .Range(.Range("Controls"), .Cells(iTabOrderCount + 1, 3))
End With
 
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.
 
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.
 
You must log in or register to reply here.
Back
Top