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
|