VBA Code to Rename a Defined Range

[azbasketcat]

I am trying to find a way to use VBA to change the name of a Defined Range.

Everything I have found so far creates a new range and then deletes the old range. I have used the existing named ranges throughout a large workbook, so I don't want to delete the existing named ranges.

Also, I have the names of the ranges I want to rename (a subset of all the named ranges in the workbook) in a range called "ExistingNames" and then I have another named range called "NewNames" which has the new name. My hope is to have the macro loop through ExistingNames and for each range assign the new name.

Any suggestions would be greatly appreciated. Thanks

 

[JoeMo]

Im trying to use your macro to change my named ranges names in the Name Manager.

Created a list, 2 columns and named that range as NameList.
Column A = Current Named Ranges Names
Column B = New Named Ranges Names

Changed code to reference Sheet10, thats where my list is.
Ran Macro, No changes in the Name Manager.

No error or compile messages. Mouse icon changes to the hour glass for a few seconds and returns to normal.

Any idea what I might be doing wrong?

Post the exact macro you are running and tell us where it is installed, standard module in the active workbook, or sheet module or …? What is the range the list in sheet10, occupies ? Is the macro run from sheet10?

 

[Bnagel07]

Post the exact macro you are running and tell us where it is installed, standard module in the active workbook, or sheet module or …? What is the range the list in sheet10, occupies ? Is the macro run from sheet10?

Sub NameChanger()
    Dim arNames()
    Dim nm As Name
    Dim i As Integer
    
    arNames = Sheets("Sheet10").Range("NameList").Value
    For i = LBound(arNames) To UBound(arNames)
        For Each nm In ActiveWorkbook.Names
            If nm.Name = arNames(i, 1) Then
                nm.Name = arNames(i, 2)
            End If
        Next nm
    Next i
End Sub

This macro is placed under Modules\ Module 1 (not a class-module)

List range is A1 to B807

When running the macro i have sheet10 open,
running macro by going to Developer Tab \ Macro and selecting NameChanger

 

[JoeMo]

I should have looked through this old thread before I replied to you. I have no access to a computer right now. Read through this thread carefully and try Jerry Sullivan’s code that returns a message if there is a problem with nothing happening.

 

[Bnagel07]

I should have looked through this old thread before I replied to you. I have no access to a computer right now. Read through this thread carefully and try Jerry Sullivan’s code that returns a message if there is a problem with nothing happening.

i did that as well and there is actually 3 different versions of the code. Tried all 3, no errors, and names still not updated.

 

[JoeMo]

Any formulas that use existing names (see post #14)?

 

[Bnagel07]

Any formulas that use existing names (see post #14)?

Only one formula that uses two of the named ranges. My document is pretty much fully controlled with VBA.

 

[davelukas22]

Is there a simple formula to have a cell's value be equal to its Named Range? (I am only using range names that are 1 cell large)

for example =RangeName

BTW, these macros works so well, thank you to everyone on this thread. Now I am trying to figure out a way to extract my range names easily.