Naming Ranges

  • Thread starter Thread starter James Montgomery
  • Start date Start date
J

James Montgomery

Hi,
I have used the recorder to Name a range and it gave me the following

ActiveWorkbook.Names.Add Name:="Stop1", RefersToR1C1:="=Sheet1!R7C3"



I really need to name the range R7C3 with "Stop1" on every sheet in the
Active Workbook and some of my workbooks vary in the number of sheets



Can someone help me?

Thank You
 
This worked ok for me:

Option Explicit
Sub testme01()
Dim wks As Worksheet
For Each wks In ActiveWorkbook.Worksheets
With wks
.Names.Add Name:="stop1", RefersTo:=.Range("c7")
End With
Next wks
End Sub


The name was added to the worksheet's collection of names--not the workbook's
collection. It seemed easier to me.
 
Thank you very much, it work great
James

Dave Peterson said:
This worked ok for me:

Option Explicit
Sub testme01()
Dim wks As Worksheet
For Each wks In ActiveWorkbook.Worksheets
With wks
.Names.Add Name:="stop1", RefersTo:=.Range("c7")
End With
Next wks
End Sub


The name was added to the worksheet's collection of names--not the workbook's
collection. It seemed easier to me.
Click to expand...
 
Be careful NOT to add duplicate scope names...
as this may lead to unexpected results.

Worksheets(1).activate
Names.add "Stop1","$A$1"
means you create a global name.. (parent = BOOK)

Worksheets(1).names.add "stop1", "$A$2" (parent = WORKSHEET)
creates a 'local' name

BOTH names now exist.
The local name will be evaluated first (if found),
and the global name is blocked.. you cannot change,evaluate or delete
it, IF a local 'sibling'exists..

=stop1 => will give you sheet1!$a$2 (when called form a cell on sheet1


now activate worksheets(2)
=Stop1 refers to the GLOBAL name (sheet1!$a$1)

delete the local name on any sheet.
Then delete the global name
recreate the local name






keepITcool

< email : keepitcool chello nl (with @ and .) >
< homepage: http://members.chello.nl/keepitcool >


James Montgomery said:
Thank you very much, it work great
James
Click to expand...
 
Just to add to keepITcool's post:

If you're going to work with names, get a copy of 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

You'll be able to review what you did very easily.
Be careful NOT to add duplicate scope names...
as this may lead to unexpected results.

Worksheets(1).activate
Names.add "Stop1","$A$1"
means you create a global name.. (parent = BOOK)

Worksheets(1).names.add "stop1", "$A$2" (parent = WORKSHEET)
creates a 'local' name

BOTH names now exist.
The local name will be evaluated first (if found),
and the global name is blocked.. you cannot change,evaluate or delete
it, IF a local 'sibling'exists..

=stop1 => will give you sheet1!$a$2 (when called form a cell on sheet1

now activate worksheets(2)
=Stop1 refers to the GLOBAL name (sheet1!$a$1)

delete the local name on any sheet.
Then delete the global name
recreate the local name



keepITcool

< email : keepitcool chello nl (with @ and .) >
< homepage: http://members.chello.nl/keepitcool >
Click to expand...
 
Just to add to keepITcool's reply, if you want to delete the global name but
keep the local name, insert a new worksheet, open the Define Names dialog,
select the global name in the list and click 'Delete'. The global name will
be deleted from the workbook, leaving only the local name(s) on its
respective sheet(s).

The new sheet will show the global names only because it won't have any
local names. The local name will still show for the sheet(s) it exists on.
GS

keepITcool said:
Be careful NOT to add duplicate scope names...
as this may lead to unexpected results.

Worksheets(1).activate
Names.add "Stop1","$A$1"
means you create a global name.. (parent = BOOK)

Worksheets(1).names.add "stop1", "$A$2" (parent = WORKSHEET)
creates a 'local' name

BOTH names now exist.
The local name will be evaluated first (if found),
and the global name is blocked.. you cannot change,evaluate or delete
it, IF a local 'sibling'exists..

=stop1 => will give you sheet1!$a$2 (when called form a cell on sheet1


now activate worksheets(2)
=Stop1 refers to the GLOBAL name (sheet1!$a$1)

delete the local name on any sheet.
Then delete the global name
recreate the local name






keepITcool

< email : keepitcool chello nl (with @ and .) >
< homepage: http://members.chello.nl/keepitcool >
Click to expand...
 
Back
Top