If you include the sheet name in the name, then that name is local to that
sheet.

Manually, it would be like entering this:

sheet1!myName
in the "Names in workbook" box.

In code:

ActiveWorkbook.Names.Add Name:="Sheet1!myRange", _
RefersToR1C1:="=Sheet1!R1C1:R10C1"

Or maybe even easier to see:

Activesheet.names.add Name:="myRange", RefersToR1C1:="=R1C1:R10C1"

I kind of like this style:

With ActiveSheet
.Range("A1:A10").Name = "'" & .Name & "'!myRange"
End With

The single quotes aren't always required--but they don't harm anything.

And if you're working with names, do yourself a giant favor and get Jan Karel
Pieterse's (with Charles Williams and Matthew Henson) Name Manager

You can find it at:
NameManager.Zip from http://www.oaltd.co.uk/mvp

Simon Shaw wrote:

Hi,

When I name a Range on Sheet1, then make a Copy of Sheet1 to say Sheet2,
then access the Define Names dialogbox, I can see the named Range listed
twice. One is noted as belonging specifically to Sheet2 and the other appears
without a sheet reference.

When assiging a name to a Range, can I specify that it belongs to a certain
sheet?

Also, how would I change this code to assign the sheet name aswell?

ActiveWorkbook.Names.Add Name:="myRange", RefersToR1C1:="=Sheet1!R1C1:R10C1"

I am trying to assign a the name 'Column_Header_Range' to each sheet in my
workbook and I want to do it through a macro

Thanks

Simon Shaw

thanks

Simon

--

Dave Peterson