Global Update of Excel "Names"

Patrick O'Dea
Patrick O'Dea used Ask the Experts™
on
Pretty straight forward question!

I have a relatively complex spreadsheet that uses "Names" rather than actual values.
Example: Cell A25 might have a name "SalesYearToDate"

There are literally hundreds of "names" which have a standardized naming structure.

Question: I want to do a global replace of many of my names.  So for instances I want to change "SalesYearToDate" to "PurchasesYearToDate"  and also "SalesTargets" to "PurchaseTargets"  etc (100 times)

How?

(I am familiar with VBA , not an expert though).
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2008
Commented:
I have the macro and the setup in this file, create a tab called Names to replace and put your list of Replacee / Replacers.

Thomas
Sub replaceNames()
Dim lRowLoop As Long, lLastRow As Long, sht As Worksheet

With Sheets("NamesToReplace")

    lLastRow = .Cells(Rows.Count, 1).End(xlUp).Row
    
    For lRowLoop = 1 To lLastRow
        For Each sht In ActiveWorkbook.Worksheets
            On Error Resume Next
                
                If sht.Name <> .Name Then
                    Debug.Print sht.Names(.Cells(lRowLoop, 1).Value).Name
                    sht.Names(.Cells(lRowLoop, 1).Value).Name = .Cells(lRowLoop, 2).Value
                End If
                
            Err.Clear
            On Error GoTo 0
        Next sht
    Next lRowLoop

End With 'Sheets("NamesToReplace")


End Sub

Open in new window

NameReplacor.xls

Author

Commented:
Thanks Thomas,

I have tried your solution but not got it working yet.
Perhaps you might have a view at the attached.

I have added a new sheet called "Test" and cell A1 is named "SalesTargets". Okay?

So I would expect your VBA to change this name to "PurchasesTargets"
But nothing changes when I run the macro.

Any thoughts??

Thanks again.



NameReplacor.xls
Top Expert 2008

Commented:
That's because your name is defined at the workbook and not the worksheet level.

Here is an updated macro for that.

Sub replaceNames()
Dim lRowLoop As Long, lLastRow As Long, sht As Worksheet

With Sheets("NamesToReplace")

    lLastRow = .Cells(Rows.Count, 1).End(xlUp).Row
    
    For lRowLoop = 1 To lLastRow
            On Error Resume Next

activeworkbook.Names(.Cells(lRowLoop, 1).Value).Name = .Cells(lRowLoop, 2).Value

        For Each sht In ActiveWorkbook.Worksheets

                
                If sht.Name <> .Name Then
                    sht.Names(.Cells(lRowLoop, 1).Value).Name = .Cells(lRowLoop, 2).Value
                End If
                
            Err.Clear
        Next sht
            On Error GoTo 0
    Next lRowLoop

End With 'Sheets("NamesToReplace")


End Sub

Open in new window

Introduction to Web Design

Develop a strong foundation and understanding of web design by learning HTML, CSS, and additional tools to help you develop your own website.

Author

Commented:
Thanks Thomas,

That works!

One thing I may have not made clear though!

I need to make changes to portions of the name (not the entire name)

So for example;

Jan10Sales  needs to change to Jan10Purchases
Feb10Sales needs to change to Feb10Purchases
Mar10Sales needs to change to Mar10Purchases
Apr10Sales needs to change to Apr10Purchases

And so on for hundreds of entries.


Thanks again!
Commented:
Try this

Sub SwapText()
    Const iCompareType As Integer = vbTextCompare   'vbBinaryCompare  [choose which type of compare]
    Const sOldText As String = "Sales"
    Const sNewText As String = "Purchase"
    Dim vItem As Object
    Dim iPosition As Integer
   
    For Each vItem In ThisWorkbook.Names
        iPosition = InStr(1, vItem.Name, "!", iCompareType)
        iPosition = InStr(iPosition + 1, vItem.Name, sOldText, iCompareType)
        If iPosition <> 0 Then
            vItem.Name = Replace(vItem.Name, sOldText, sNewText, iPosition, iCompareType)
        End If
    Next

End Sub


I do a check to see if it is a workbook name or a sheet name and if its a sheet name, start the replace after the ! in case a sheet has the word Sales in it.
Just change the values of the constants to suite your needs.
I have also added options for two types of compare. i.e. binarycompare will match case but textcompare will not. Just edit the line as require

Hope this helps

James

Author

Commented:
Thanks folks , just perfect!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial