retrieving named ranges from another sheet

[a7zulu]

I currently do this inside a subroutine

Set Holidays_r = Names("Holidays").RefersToRange

to access a certain range on a sheet.

I was wondering how would I access the same named range that exists on
a seperate sheet that may or may not be opened? Do I have to open
that workbook first?


Thanks

 

[Guest]

So I assume that this named range is in a seperate workbook. If that is the
case then yes you need to open the other workbook. At that point it is fairly
easy, just make sure that you are explicit in your referencing as it becomes
more important when you are dealing with multiple open workbooks.

 

[Ronald Dodge]

To your 2 part question:

Question 1: Does the workbook with the range name need to be open?

Answer: Yes. It's the same thing that I have stated about calculations.
Calculations from a closed workbook should take place, but it doesn't seem
to work, but then I also have more complex formulas in some of my workbooks,
thus why the "F9" key doesn't work for full calculation in manual
calculation mode, which is why I strictly use the "Ctrl-Alt-F9" key
combination for full calculations.

Same type deal, for VBA to see the objects, they must be opened to be able
to read the objects and their related attributes.

Question 2: How do I have it refer to a different workbook that the range
name is in?

Answer: The way you have the "Names" collection object reference, it's
refering to the active workbook. If you don't like the symptoms that it
gives or need to specify which workbook object to refer to as the Names
collection is part of a workbook object, then use the following example:

'Assuming "Book1.xls" is already open, and the range name of "Holidays"
exist in "Book1.xls"

Dim wbkBook1 As Excel.Workbook, rngHolidays As Excel.Range
Set wbkBook1 = Workbooks("Book1.xls")
Set rngHolidays = wbkBook1.Names("Holidays").RefersToRange


--

Sincerely,

Ronald R. Dodge, Jr.
Master MOUS 2000