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" wrote :

Thank you very much, it work great
James

"Dave Peterson" wrote in message
...
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 wrote:

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