Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Rename all named ranges?
Excel 2000
I have a workbook with 100+ named ranges. I would like to rename them all. Starting all names with "R1" and the old name. I would also like all the formulas in the workbook that refers to the names be changed to the new names. (perhaps this happened automatically?) Thanks Johan |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Rename all named ranges?
You can't use R1, but you could use R_
Dim nme As Name For Each nme In ActiveWorkbook.Names ActiveWorkbook.Names.Add Name:="R_" & nme.name, RefersTo:=nme.RefersTo nme.Delete Next nme -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Johan" wrote in message ups.com... Excel 2000 I have a workbook with 100+ named ranges. I would like to rename them all. Starting all names with "R1" and the old name. I would also like all the formulas in the workbook that refers to the names be changed to the new names. (perhaps this happened automatically?) Thanks Johan |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Rename all named ranges?
Bob supplied code to rename the names, but that code won't fix any formulas.
Those will be broken after Bob's code runs. If those range names are unique strings.... You may be able to run Bob's code (without the nme.delete line) Then do a bunch of edit|replaces through each worksheet to replace the old names with the new names. Then go back to delete the old names. But this won't affect names used in other names (insert|name) or any VBA code either! If you had a specific pattern of names (or list of names), then you may get more responses on what to do--but whatever you do, make sure it's against a copy of your workbook. Too much could go wrong. And since you're working with names... Get Jan Karel Pieterse's (with Charles Williams and Matthew Henson) Name Manager to make checking those names easier. You can find it at: NameManager.Zip from http://www.oaltd.co.uk/mvp === And get Bill Manville's FindLink program: http://www.oaltd.co.uk/MVP/Default.htm To help find any thing you broke after you do this change. Bob Phillips wrote: You can't use R1, but you could use R_ Dim nme As Name For Each nme In ActiveWorkbook.Names ActiveWorkbook.Names.Add Name:="R_" & nme.name, RefersTo:=nme.RefersTo nme.Delete Next nme -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Johan" wrote in message ups.com... Excel 2000 I have a workbook with 100+ named ranges. I would like to rename them all. Starting all names with "R1" and the old name. I would also like all the formulas in the workbook that refers to the names be changed to the new names. (perhaps this happened automatically?) Thanks Johan -- Dave Peterson |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Rename all named ranges?
can' help with replacing the formulas, you can try the other suggestions. but
when i want to do this, i just use this code to create the new ranges in the immediate window. then just paste the code created in a module and run it. keeps my installed addins to a minimum, and plus, i don't know if my clients will have every addin i have, either. Sub RenameRanges() ' recreate and then delete range names Dim nm As Name sName = ActiveSheet.Name For Each nm In ThisWorkbook.Names Debug.Print "ActiveWorkbook.Names.Add Name:=" & """" & "R_" & nm.Name & """" & _ ", Refersto:=""" & nm nm.Delete Next End Sub -- Gary "Johan" wrote in message ups.com... Excel 2000 I have a workbook with 100+ named ranges. I would like to rename them all. Starting all names with "R1" and the old name. I would also like all the formulas in the workbook that refers to the names be changed to the new names. (perhaps this happened automatically?) Thanks Johan |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Rename all named ranges?
Thanks for your answers, but isn't it possible to have the search and
replace procedure for the workbook names done automatically before the old names are deleted? Like this: For each name in file Copy old name "Box" to new name "R_Box" Search for "Box" in workbook formulas and replace with "R_Box" Delete the name "Box" Next name Regards, Johan |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Rename all named ranges?
Maybe.
But you never shared the rules or list for what names should be replaced. Johan wrote: Thanks for your answers, but isn't it possible to have the search and replace procedure for the workbook names done automatically before the old names are deleted? Like this: For each name in file Copy old name "Box" to new name "R_Box" Search for "Box" in workbook formulas and replace with "R_Box" Delete the name "Box" Next name Regards, Johan -- Dave Peterson |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Rename all named ranges?
give this a try
Sub RenameRanges() Dim nm As Name Dim sname As String Dim v1 As String Dim ws As Worksheet Dim NewName As String For Each nm In ThisWorkbook.Names sname = Mid(nm, 2, InStr(nm, "!") - 2) v1 = "r_" & nm.Name ActiveWorkbook.Names.Add Name:=v1, RefersTo:=nm With Worksheets(sname).UsedRange .Replace what:=nm.Name, replacement:=v1, _ SearchOrder:=xlByColumns, MatchCase:=False End With nm.Delete Next End Sub -- Gary "Johan" wrote in message ups.com... Thanks for your answers, but isn't it possible to have the search and replace procedure for the workbook names done automatically before the old names are deleted? Like this: For each name in file Copy old name "Box" to new name "R_Box" Search for "Box" in workbook formulas and replace with "R_Box" Delete the name "Box" Next name Regards, Johan |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Rename all named ranges?
Thanks for helping me, I think the solution is near.
Dave peterson wrote "But you never shared the rules or list for what names should be replaced" I would like all the names to be replaced and I'm only using names in the workbook (not in code). I'm using names only for ranges, not dynamic ranges or formulas. I tried Gary's code but I got this error message: " subscript out of range" Sub RenameRanges() Dim nm As Name Dim sname As String Dim v1 As String Dim ws As Worksheet Dim NewName As String For Each nm In ThisWorkbook.Names sname = Mid(nm, 2, InStr(nm, "!") - 2) v1 = "r_" & nm.Name ActiveWorkbook.Names.Add Name:=v1, RefersTo:=nm With Worksheets(sname).UsedRange ''''''''''''''''''''''''''''here I get " subscript out of range" .Replace what:=nm.Name, replacement:=v1, _ SearchOrder:=xlByColumns, MatchCase:=False End With nm.Delete Next End Sub I have tried to modife the code, but I han't got it 100% right. Any ideas what I should do? Thanks, Johan |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Rename all named ranges?
when i go to insert/name/define and click on a name, i get something like the
following in the refers to box =Sheet1!$D$8:$G$13 what do you see? all that line of code does is extract the sheet name from the named range, "sheet1" maybe give an example of your named ranges and a range it refers to. -- Gary "Johan" wrote in message oups.com... Thanks for helping me, I think the solution is near. Dave peterson wrote "But you never shared the rules or list for what names should be replaced" I would like all the names to be replaced and I'm only using names in the workbook (not in code). I'm using names only for ranges, not dynamic ranges or formulas. I tried Gary's code but I got this error message: " subscript out of range" Sub RenameRanges() Dim nm As Name Dim sname As String Dim v1 As String Dim ws As Worksheet Dim NewName As String For Each nm In ThisWorkbook.Names sname = Mid(nm, 2, InStr(nm, "!") - 2) v1 = "r_" & nm.Name ActiveWorkbook.Names.Add Name:=v1, RefersTo:=nm With Worksheets(sname).UsedRange ''''''''''''''''''''''''''''here I get " subscript out of range" .Replace what:=nm.Name, replacement:=v1, _ SearchOrder:=xlByColumns, MatchCase:=False End With nm.Delete Next End Sub I have tried to modife the code, but I han't got it 100% right. Any ideas what I should do? Thanks, Johan |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
rename unknown named files | Excel Programming | |||
union of named ranges based only on the names of those ranges | Excel Programming | |||
Copy data in named ranges to a newer version of the same template to identical ranges | Excel Programming | |||
Like 123, allow named ranges, and print named ranges | Excel Discussion (Misc queries) | |||
named ranges - changing ranges with month selected | Excel Programming |