Named Range - help!
 

Say I have 3 sheets each with a named range on.

I know I can use the collection to cycle thru all names...

But how can I work out the sheet on which the name is?

Any sort of pointer would help!

thanks,
Matt.


--
MattShoreson
------------------------------------------------------------------------
MattShoreson's Profile: http://www.excelforum.com/member.php...fo&userid=3472
View this thread: http://www.excelforum.com/showthread...hreadid=548988

Named Range - help!
 

To list the names of the ranges and the sheets they exist in:

for each n in thisworkbook.names
sheet1.cells(n.index,1).value = n.name
sheet1.cells(n.index,2).value
mid(n.RefersTo,2,instr(n.RefersTo,"!")-2)
next


Co

--
colofnatur
-----------------------------------------------------------------------
colofnature's Profile: http://www.excelforum.com/member.php...fo&userid=3435
View this thread: http://www.excelforum.com/showthread.php?threadid=54898

Named Range - help!
 

How about this, if you are using the names collection of the workbook:

?thisworkbook.Names(1).RefersToRange.Worksheet.Nam e

Jeff

"MattShoreson"
wrote in message
news:MattShoreson.28zjd1_1149605752.6187@excelforu m-nospam.com...

Say I have 3 sheets each with a named range on.

I know I can use the collection to cycle thru all names...

But how can I work out the sheet on which the name is?

Any sort of pointer would help!

thanks,
Matt.


--
MattShoreson
------------------------------------------------------------------------
MattShoreson's Profile:
http://www.excelforum.com/member.php...fo&userid=3472
View this thread: http://www.excelforum.com/showthread...hreadid=548988

Named Range - help!
 

cheers col - mental block on my part.

Forgot the sheetname was in the reference

--
MattShoreso
-----------------------------------------------------------------------
MattShoreson's Profile: http://www.excelforum.com/member.php...nfo&userid=347
View this thread: http://www.excelforum.com/showthread.php?threadid=54898

Named Range - help!
 

Dim ws as Worksheet
set ws = Range(myName").Worksheet

HTH
--
AP

"MattShoreson" a
écrit dans le message de news:
...

Say I have 3 sheets each with a named range on.

I know I can use the collection to cycle thru all names...

But how can I work out the sheet on which the name is?

Any sort of pointer would help!

thanks,
Matt.


--
MattShoreson
------------------------------------------------------------------------
MattShoreson's Profile:
http://www.excelforum.com/member.php...fo&userid=3472
View this thread: http://www.excelforum.com/showthread...hreadid=548988