Defined Name (XP)

Topic

New Reply

Is there a way to change a defined name globally in excel…(across multiple worksheets)

Reply | Quote

Replies

Not sure what you mean exactly.

By default all names are “global” (across multiple worksheets)

You can make a “local” name (within a particular worksheet) by prefacing it with the sheetname when you are inserting the name:
Sheet1!MyName

to define MyName to a range/formula.

You can (in another sheet) enter:
MyName

as a name and it will be “global” (except for sheet1, since that has a local copy of that name) and usable by all other sheets.

Steve

Reply | Quote

Right..lets say I have several names across various worksheets and they are say smith03 and now its o4 and I want to change them all at one time?

Reply | Quote

Right..lets say I have several names across various worksheets and they are say smith03 and now its o4 and I want to change them all at one time?

Reply | Quote

If you want the name (of the name) to change but keep what it refers to the same, you could use something like:

Sub ChangeNames()
    Dim strOldName As String, strNewName As String
    Dim wks As Worksheet
    strOldName = "Smith03"
    strNewName = "Smith04"
    For Each wks In ActiveWorkbook.Worksheets
        With wks
            .Names.Add strNewName, .Names(strOldName).RefersTo
            .Names(strOldName).Delete
        End With
    Next wks
End Sub

Hope that helps.

Reply | Quote

If you want the name (of the name) to change but keep what it refers to the same, you could use something like:

Sub ChangeNames()
    Dim strOldName As String, strNewName As String
    Dim wks As Worksheet
    strOldName = "Smith03"
    strNewName = "Smith04"
    For Each wks In ActiveWorkbook.Worksheets
        With wks
            .Names.Add strNewName, .Names(strOldName).RefersTo
            .Names(strOldName).Delete
        End With
    Next wks
End Sub

Hope that helps.

Reply | Quote

Something like this?

Option Explicit
Sub ChangeNames()
    Dim nm As Name
    Dim sOld As String
    Dim sNew As String
    sOld = "smith03"
    sNew = "o4"
    For Each nm In ActiveWorkbook.Names
        If nm.Name = sOld Then
            ActiveWorkbook.Names.Add _
                Name:=sNew, _
                RefersTo:=nm.RefersTo
            nm.Delete
        End If
    Next
End Sub

Note: this will only change global names, local names are prefaced with sheetname so you would have to loop thru each sheetname to find all of them.

Steve

Reply | Quote

Something like this?

Option Explicit
Sub ChangeNames()
    Dim nm As Name
    Dim sOld As String
    Dim sNew As String
    sOld = "smith03"
    sNew = "o4"
    For Each nm In ActiveWorkbook.Names
        If nm.Name = sOld Then
            ActiveWorkbook.Names.Add _
                Name:=sNew, _
                RefersTo:=nm.RefersTo
            nm.Delete
        End If
    Next
End Sub

Note: this will only change global names, local names are prefaced with sheetname so you would have to loop thru each sheetname to find all of them.

Steve

Reply | Quote

The other guys gave you some code to change the name itself. Alas there is (as yet) no utility that can rename the names everywhere they are used.

You might try using my flexfind utility.
Make sure you check the “objects” box if you need to search in other things besides cells.

Be careful though, flexfind does not check whether the name is really cleanly found. If you search for “Name1”, it will also find “Name11”, “Name12” or “Name1w”.
So if you are planning to do a S&R, make sure you check and acknowledge each and every occurrence.

Reply | Quote

The other guys gave you some code to change the name itself. Alas there is (as yet) no utility that can rename the names everywhere they are used.

You might try using my flexfind utility.
Make sure you check the “objects” box if you need to search in other things besides cells.

Be careful though, flexfind does not check whether the name is really cleanly found. If you search for “Name1”, it will also find “Name11”, “Name12” or “Name1w”.
So if you are planning to do a S&R, make sure you check and acknowledge each and every occurrence.

Reply | Quote

Not sure what you mean exactly.

By default all names are “global” (across multiple worksheets)

You can make a “local” name (within a particular worksheet) by prefacing it with the sheetname when you are inserting the name:
Sheet1!MyName

to define MyName to a range/formula.

You can (in another sheet) enter:
MyName

as a name and it will be “global” (except for sheet1, since that has a local copy of that name) and usable by all other sheets.

Steve

Reply | Quote

If you are looking for a way to create the same local name on each sheet and have it refer to a cell on that local sheet, here is an example:

Option Explicit
Sub CreateLocalNames()
    Dim wks As Worksheet
    Dim sName As String
    Dim sRefersTo As String
    sName = "Bill"
    sRefersTo = "$A$15"
    For Each wks In Worksheets
        ActiveWorkbook.Names.Add _
            Name:="'" & wks.Name & "'!" & sName, _
            RefersTo:="='" & wks.Name & "'!" & sRefersTo
    Next
End Sub

It will create the named range “Bill” and it will refer to cell A15 of the sheet it is called on. You could have “Bill” also be different for each sheet if desired.

Steve

Reply | Quote

If you are looking for a way to create the same local name on each sheet and have it refer to a cell on that local sheet, here is an example:

Option Explicit
Sub CreateLocalNames()
    Dim wks As Worksheet
    Dim sName As String
    Dim sRefersTo As String
    sName = "Bill"
    sRefersTo = "$A$15"
    For Each wks In Worksheets
        ActiveWorkbook.Names.Add _
            Name:="'" & wks.Name & "'!" & sName, _
            RefersTo:="='" & wks.Name & "'!" & sRefersTo
    Next
End Sub

It will create the named range “Bill” and it will refer to cell A15 of the sheet it is called on. You could have “Bill” also be different for each sheet if desired.

Steve

Reply | Quote

As a footnote to Steve’s explanation, if you have a series of sheet-local names that are the same (Sheet1!MySameName, Sheet2!MySameName, Sheet3!MySameName, etc.) you should find MS Excel MVP Jan-Karel Pieterse‘s Name Manager of use.

Reply | Quote

As a footnote to Steve’s explanation, if you have a series of sheet-local names that are the same (Sheet1!MySameName, Sheet2!MySameName, Sheet3!MySameName, etc.) you should find MS Excel MVP Jan-Karel Pieterse‘s Name Manager of use.

Reply | Quote