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 wrote:

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

--
HTH

Bob Phillips

"Mats Samson" wrote in message
...
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


--

Dave Peterson