Quick way to rename Range Names?

[Sue Gould]

Hi - I have a workbook with over 1000 Named Ranges in it (most appear 12 times - as there are a few sheets for each month, and each has the same Named Ranges)

I also have many macros that include the specific Named Ranges. I am about to implement this workbook (which works perfectly) in 53 different branches.

Problem is, I originally created it many years ago in Excel 2003. If I save it into Excel 2007, it renames many of the Ranges, which means the macros don't work.

I am hoping to will be able to upgrade the branches to 2007 or 2010 in a few months - so need the workbook to work under both 2003 and 2007/2010.

If I can rename all the ranges now, I can also change the macros to match.

BUT - is there a quick way to rename 1000s of Range Names quickly, or will I have to do them all individually? Remembering also, many are repeated 12 times.

Any advice / suggestions welcomed.

Thanks, Sue

 

[Ralajer]

Sue,

You can loop through all names in a workbook and rename based on some criteria. In the code below it uses the sheet name in the RefersTo property. I also had some trouble simply renaming the Named Ranges so I delete and then add with the new name.

Sub NamedRangesRename()

    Dim NRs() As String
    Dim i As Integer
    Dim newName As String
    Dim SheetName As String
    
    NRs = NamedRangesGet()
    
    For i = 1 To UBound(NRs)
        '// Adds the sheet name being refered To to end of existing name
        '// So the named range ITEMS on October Sheet become ITEMS_October
        SheetName = "_" & Mid(NRs(i, 2), 2, InStr(1, NRs(i, 2), "!") - 2)
        '// Replace Spaces. Other change may have to be made based on the
        '// Naming rules for named ranges
        SheetName = Replace(SheetName, " ", "_")
        
        newName = NRs(i, 1) & SheetName
        '// Delete the old Named Range
        ActiveWorkbook.Names(NRs(i, 1)).Delete
        '// Create Named range
        ActiveWorkbook.Names.Add Name:=newName, RefersTo:=NRs(i, 2)
    Next i

End Sub

This function extracts the name and refersto values and returns a 2D array containing that information.

Function NamedRangesGet() As String()

    Dim NR As Name
    Dim i As Integer
    Dim arrNR() As String

    ReDim arrNR(1 To Names.Count, 1 To 2)
    
    i = 1
'// Loop thru named ranges and extract name and refersTo range
    For Each NR In Names
        arrNR(i, 1) = NR.Name
        arrNR(i, 2) = NR.RefersTo
        i = i + 1
    Next
    NamedRangesGet = arrNR
End Function

There are some issues with this but may be a good starting point. Possible issues:
Named Range that refer to a constant rather than a range
Naming Rule for NR don't match those for sheets.
Code was written and tested in Excel 2010 not sure if all the same applies to 2003.


A less automatic solution would be to output the named ranges' values to a worksheet and in adjacent cell create the new name manually or with a formula that might be a better way to catch exception. Then run code similar to the NamedRangesRename sub but referring to the sheet rather than an array.

 

[Sue Gould]

Thanks for that Rob - appreciate your time.

I will try this and let you know how I get on (probably not until middle of next week - will be out of action for a few days)

Thanks, Sue