#1   Report Post  
Simon Shaw
 
Posts: n/a
Default 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   Report Post  
Dave Peterson
 
Posts: n/a
Default

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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Sorting Multiple Ranges simoneaux Excel Worksheet Functions 1 February 8th 05 04:11 PM
Naming Ranges Donna In Denver Excel Discussion (Misc queries) 1 January 28th 05 08:48 AM
Problem with graph ranges No Such Luck Charts and Charting in Excel 6 December 3rd 04 02:09 PM
compare unique identifiers in multiple ranges bawilli_91125 Charts and Charting in Excel 1 November 30th 04 07:34 PM
Named dynamic ranges, copied worksheets and graph source data WP Charts and Charting in Excel 1 November 28th 04 06:19 PM


All times are GMT +1. The time now is 10:54 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"