Excel - RE-naming ranges in Name Manager - How to change the Scope

Posted on 2012-09-02
Medium Priority
Last Modified: 2012-09-16
The scope is grayed out when I edit a named range.  How can I change the scope when editing a range? (See graphic)
Question by:brothertruffle880
  • 2
  • 2
LVL 20

Accepted Solution

TheAvenger earned 2000 total points
ID: 38358548
You cannot change the scope once the name is created. You need to delete it and recreate it from the name manager. Just go to the name manager and click the new button, then select the sheet you want to have the name in (see screenshot).

You can read more about scopes here: http://office.microsoft.com/en-us/excel-help/define-and-use-names-in-formulas-HA010147120.aspx
LVL 50

Expert Comment

by:Martin Liss
ID: 38358840
You cannot change the scope once the name is created

Sorry but that's not correct. Either go to the Name Manager and click the Edit button at the top and then change what's in 'Refers to' or click the tiny spreadsheet-like button near the lower righthand corner and that will take you to a 'Refers to' box where you can make the change.
LVL 50

Expert Comment

by:Martin Liss
ID: 38358845
Oops. I didn't notice that you were talking about the scope rather than the range itself.
LVL 20

Expert Comment

ID: 38358849
Exactly ;-)
LVL 17

Expert Comment

ID: 38361807
If you want to automate this, try this VBA code.  It takes as a parameter the name of a Name with workbook scope, and changes it to local worksheet scope.  Obviously this would fail if the named range did not refer to a valid range,

Public Sub ChangeScopeToLocal(ByVal strGlobalName As String)

    Dim n As Excel.Name
    Dim r As Excel.Range
    Dim strName As String
    Set n = ActiveWorkbook.Names(strGlobalName)
    Set r = n.RefersToRange
    strName = n.Name
    r.Worksheet.Names.Add Name:=strName, RefersTo:=r

End Sub

Public Sub Test()

    ChangeScopeToLocal "Stuart"

End Sub

Open in new window


Featured Post

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
After seeing numerous questions for Dynamic Data Validation I notice that most have used Visual Basic to solve the problem. This suggestion is purely formula based and can be used in multiple rows.
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question