Global Update of Excel "Names"

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).
Patrick O'DeaAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

nutschCommented:
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
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Patrick O'DeaAuthor 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
0
nutschCommented:
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

0
Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

Patrick O'DeaAuthor 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!
0
jmac01Commented:
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
0
Patrick O'DeaAuthor Commented:
Thanks folks , just perfect!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.