asked on

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).

ASKER CERTIFIED SOLUTION

nutsch

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Patrick O'Dea

ASKER

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

nutsch

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

Patrick O'Dea

ASKER

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!

SOLUTION

jmac01

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Patrick O'Dea

ASKER

Thanks folks , just perfect!

Global Update of Excel &quot;Names&quot;

Global Update of Excel "Names"