Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Naming Ranges
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 |
#2
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sorting Multiple Ranges | Excel Worksheet Functions | |||
Naming Ranges | Excel Discussion (Misc queries) | |||
Problem with graph ranges | Charts and Charting in Excel | |||
compare unique identifiers in multiple ranges | Charts and Charting in Excel | |||
Named dynamic ranges, copied worksheets and graph source data | Charts and Charting in Excel |