Naming Ranges

[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

 

[Dave Peterson]

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.

 

[James Montgomery]

Thank you very much, it work great
James

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.

 

[keepITcool]

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 >

Thank you very much, it work great
James

 

[Dave Peterson]

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 >

 

[Guest]

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

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 >