Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Naming Ranges
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Naming Ranges
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Naming Ranges
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Naming Ranges
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Naming Ranges
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Naming Ranges
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" 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Naming ranges | Setting up and Configuration of Excel | |||
Naming Ranges | Excel Discussion (Misc queries) | |||
naming ranges | Excel Worksheet Functions | |||
naming ranges | Excel Worksheet Functions | |||
Naming Ranges | Excel Discussion (Misc queries) |