Referencing a range that is not on the active sheet.

[JeffreyWeir]

I've got some code that says this:

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]

Try

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

[Bob Phillips]

Or even

With Sheet6

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

[JeffreyWeir]

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]

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.