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.

keepITcool wrote:

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





--

Dave Peterson