Same Name refers to local ranges

[Guest]

I happened to copy a worksheet with a named range.
Afterwards the named range exists in both sheets as a "local" reference,
i.e. I have the same Name in both sheets and they refer to the same cell in
the respective sheets. If you look in Insert/Define you'll find a sheet
reference to the current sheet, next to the Name.
Is there a way of creating a "local" Name reference to a cell or range,
without splitting sheets? It can be very useful, especially in vba!
Best Regards
Mats

 

[KL]

Hi Mats,

I am not sure I understand your question, but if you want a defined name
which would refer to a specific range on the sheet where it is called from,
then when defining a name in the 'Refers To:' box use the following notation
("!" will prevent Excel from automatically setting the sheet reference to
the sheet where the name has been created):

=!$A$1

Regards,
KL

 

[Guest]

If you create a worksheet and name Sheet 1 to My.
Then you Define the Name Dummy to cell B2.
Copy the sheet by holding down shift and pull it rightwards and
you'll have the name My (2). Rename this sheet to Your.
If you look in cell B2 on both sheets the name Dummy exists in
both locations but seems to be "worksheet local". there is a side reference
to the sheet for the "replica" in Yours.
You can reference "Dummy" but eventual changes are only locally,
i.e made in the sheet in focus.
Can be rather useful! But I'd like to /create/use it without the awesome
copy technique!
Regards
Mats

 

[Bob Phillips]

Mats,

This will create a local name

Range("A1:B10").Name = ActiveSheet.Name & "!myRange"


See also http://www.xldynamic.com/source/xld.Names.html


You might also want to get a copy of Jan Karel Pieterse's N ame Mangaer,
very useful when working with names.
http://www.jkp-ads.com/OfficeMarketPlaceNM-EN.htm

 

[R.VENKATARAMAN]

If I remember correct if you give the range name as
sheet1!rv
it becomes local name in sheet1
if you just give name as
rv
it becomes global

try some experiments.
===============

 

[Dave Peterson]

And just in case that activesheet name needs to be enclosed in single quotes (if
it contains spaces for example):

Range("A1:B10").Name = "'" & ActiveSheet.Name & "'" & "!myRange"

 

[Bob Phillips]

Damn, I thought I had included that!

Bob

And just in case that activesheet name needs to be enclosed in single quotes (if
it contains spaces for example):

Range("A1:B10").Name = "'" & ActiveSheet.Name & "'" & "!myRange"