ranged name without "worksheet"
 

I am not too smart I guess, for I can't seem to find out how to refer
to (read) a named range when the specific work sheet is not known. (I
want to do this in VBS.)

I figured this out all on my own:

set rng = Workbook.Worksheets(1).Range("Name")

But what about when you know "Name" but not what sheet it is in?

ranged name without "worksheet"
 

I am not too sure why you would not know what sheet your named range was in
but assuming you did not know you would need to loop through all of the
sheets to find it something like this

dim wks as worksheet
dim rng as range

on error resume next
for each wks in worksheets
set rng = wks.range("Name")
if not rng is nothing then exit for
next wks
--
HTH...

Jim Thomlinson


" wrote:

I am not too smart I guess, for I can't seem to find out how to refer
to (read) a named range when the specific work sheet is not known. (I
want to do this in VBS.)

I figured this out all on my own:

set rng = Workbook.Worksheets(1).Range("Name")

But what about when you know "Name" but not what sheet it is in?

ranged name without "worksheet"
 

If you if it is a workbook level name

set rng = Workbooks("Book1.xls").Names("Name").RefersToRange

is pretty robust.

--
Regards,
Tom Ogilvy

wrote in message
oups.com...
I am not too smart I guess, for I can't seem to find out how to refer
to (read) a named range when the specific work sheet is not known. (I
want to do this in VBS.)

I figured this out all on my own:

set rng = Workbook.Worksheets(1).Range("Name")

But what about when you know "Name" but not what sheet it is in?

ranged name without "worksheet"
 

set rng = Workbooks("Book1.xls").Names("Name").RefersToRange

ah! 'RefersToRange'! thanks

(if only Microsoft had an online reference that had more than a few
examples i actually may have found a reference to RefersToRange in
their reference on Ranges!)

ranged name without "worksheet"
 

I am not too sure why you would not know what sheet
your named range was in ...

this is my reasoning:

I have a fairly large spreadsheet, with much customer data in it
(there's one "workbook" per customer actually). And recently I have
been using VBSCRIPT to automate the office -- reports etc.

To make a long story short, if I move a named range from one worksheet
to another, I do not want to have to manually search for and replace
all references to the Worksheet name in all of the VBSCRIPT code.

thanks for your reply