Cell Name / Named Range

[bk4u]

Is there a way a macro could rename part of a cell name or named range in a workbook? I would like to change the same occurrence of a character string in each cell name with another character string using VBA instead of having to edit each cell name using the Name Manager.

Thanks for any help you may be able to provide!

 

[GWteB]

How about

Public Sub bk4u()
    RenameName "SomeName", "SomeNewName"
End Sub


Public Sub RenameName(ByVal argPartToBeReplaced As String, ByVal argReplacement As String)
    Dim Nm  As Name
    For Each Nm In ThisWorkbook.Names
        Nm.Name = VBA.Replace(Nm.Name, argPartToBeReplaced, argReplacement, , , vbTextCompare)
    Next Nm
End Sub

 

[bk4u]

How about

Public Sub bk4u()
    RenameName "SomeName", "SomeNewName"
End Sub


Public Sub RenameName(ByVal argPartToBeReplaced As String, ByVal argReplacement As String)
    Dim Nm  As Name
    For Each Nm In ThisWorkbook.Names
        Nm.Name = VBA.Replace(Nm.Name, argPartToBeReplaced, argReplacement, , , vbTextCompare)
    Next Nm
End Sub

GWteB:

You are a timesaver and a sanity-saver! Many thanks for this solution!

I have had to rename approximately 3,500 cell names each month in a workbook. This workbook has a main worksheet with monthly and yearly totals. There are monthly raw data worksheets which I use for pasting in raw data each week. I then have formulas using cell names on the main worksheet read the monthly raw data on the monthly raw data worksheets. Since the position of the raw data never changes on each monthly raw data worksheet, I knew cell names would be the only way to quickly update weekly data on the main worksheet. The cell name consisted of the staff person's name, the name of the month and the week of the month. My problem was changing the character string in the middle of the cell name on the raw data worksheets for each new month's raw data.

Now your solution has given me back so much time! I am so grateful for your selfless contribution to my work. I hope to be able to help someone one day like you have helped me.

BK4U

 

[GWteB]

You are welcome and thank you for your detailed expression of gratitude. The pleasure was entirely mine. Have a nice weekend.