Qualification of Named Ranges

[Peter__]

When you refer to a named range, does it need to be qualified?

I'm sure I've referred to named ranges in the past without qualifying them with which sheet they're on, but just today I'm getting "method 'name' of object '_global' excel" failed".

Am I on drugs to think this ever worked? Or have I just been lucky that when ever I've used them in the past, the sheet containing the range just happens to be the active sheet? Or is there some other factor that effects when you need to qualify the named range and when you don't?

 

[mikerickson]

Depending on how it was scoped at creation, a Name is scoped to either a workbook or a worksheet.
Syntax like this qualifies it.
Workbooks("myWorkbook.xlsm").Names("myNamedRange").RefersToRange

Use WorkBooks("myWorkbook.xlsm").Worksheets("Sheet1") if the name is scoped a the worksheet level.

 

[Peter__]

Thanks Mike,

How do you specify the scope? I have always just type the name into the address box in the top left corner -- what scope would that have?

 

[mikerickson]

That would be workbook wide scope