Rename all named ranges?

[Johan]

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

 

[Bob Phillips]

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)

 

[Dave Peterson]

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.

 

[Gary Keramidas]

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

 

[Johan]

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]

Maybe.

But you never shared the rules or list for what names should be replaced.

 

[Gary Keramidas]

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

 

[Johan]

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

 

[Gary Keramidas]

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.