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.

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


Col

cheers col - mental block on my part.

Forgot the sheetname was in the reference!

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

?thisworkbook.Names(1).RefersToRange.Worksheet.Name

Jeff

"MattShoreson" <MattShoreson.28zjd1_1149605752.6187@excelforum-nospam.com>
wrote in message
news:MattShoreson.28zjd1_1149605752.6187@excelforum-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
>

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

HTH
--
AP

"MattShoreson" <MattShoreson.28zjd1_1149605752.6187@excelforum-nospam.com> a
écrit dans le message de news:
MattShoreson.28zjd1_1149605752.6187@...rum-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
>