Results 1 to 8 of 8
  1. #1
    Star Lounger
    Join Date
    Aug 2002
    Location
    Orlando, Florida, USA
    Posts
    82
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Defined Name (XP)

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

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Defined Name (XP)

    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

  3. #3
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Defined Name (XP)

    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:

    <pre>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</pre>


    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

  4. #4
    Star Lounger
    Join Date
    Aug 2002
    Location
    Orlando, Florida, USA
    Posts
    82
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Defined Name (XP)

    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?

  5. #5
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Defined Name (XP)

    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 <!profile=Pieterse>Pieterse<!/profile>'s Name Manager of use.
    -John ... I float in liquid gardens
    UTC -7ąDS

  6. #6
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Defined Name (XP)

    If you want the name (of the name) to change but keep what it refers to the same, you could use something like:
    <pre>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
    </pre>

    Hope that helps.
    Regards,
    Rory

    Microsoft MVP - Excel

  7. #7
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Defined Name (XP)

    Something like this?

    <pre>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</pre>


    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

  8. #8
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Defined Name (XP)

    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.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •