How to change Named Range Scope

I found the solution! Just copy the sheet with your named variables. Then delete the original sheet. The copied sheet will now have the same named variables, but with a local scope (scope= the copied sheet).

However, I don't know how to change from local variables to global..

Check out these two subs that reverse each other and flip the scope (worksheet to workbook or reverse) of all named ranges that refer to a range on the active sheet.

 Option Explicit '--------------------------------------------------------------------------------------- ' Procedure : RescopeNamedRangesToWorkbook ' Author : JS20'07'11 ' Date : 11/18/2013 ' Purpose : Rescopes the parent of worksheet scoped named ranges to the active workbook ' for each named range with a scope equal to the active sheet in the active workbook. '--------------------------------------------------------------------------------------- Public Sub RescopeNamedRangesToWorkbook() Dim wb As Workbook Dim ws As Worksheet Dim objName As Name Dim sWsName As String Dim sWbName As String Dim sRefersTo As String Dim sObjName As String Set wb = ActiveWorkbook Set ws = ActiveSheet sWsName = ws.Name sWbName = wb.Name 'Loop through names in worksheet. For Each objName In ws.Names 'Check name is visble. If objName.Visible = True Then 'Check name refers to a range on the active sheet. If InStr(1, objName.RefersTo, sWsName, vbTextCompare) Then sRefersTo = objName.RefersTo sObjName = objName.Name 'Check name is scoped to the worksheet. If objName.Parent.Name <> sWbName Then 'Delete the current name scoped to worksheet replacing with workbook scoped name. sObjName = Mid(sObjName, InStr(1, sObjName, "!") + 1, Len(sObjName)) objName.Delete wb.Names.Add Name:=sObjName, RefersTo:=sRefersTo End If End If End If Next objName End Sub '--------------------------------------------------------------------------------------- ' Procedure : RescopeNamedRangesToWorksheet ' Author : JS20'07'11 ' Date : 11/18/2013 ' Purpose : Rescopes each workbook scoped named range to the specific worksheet to ' which the range refers for each named range that refers to the active worksheet. '--------------------------------------------------------------------------------------- Public Sub RescopeNamedRangesToWorksheet() Dim wb As Workbook Dim ws As Worksheet Dim objName As Name Dim sWsName As String Dim sWbName As String Dim sRefersTo As String Dim sObjName As String Set wb = ActiveWorkbook Set ws = ActiveSheet sWsName = ws.Name sWbName = wb.Name 'Loop through names in worksheet. For Each objName In wb.Names 'Check name is visble. If objName.Visible = True Then 'Check name refers to a range on the active sheet. If InStr(1, objName.RefersTo, sWsName, vbTextCompare) Then sRefersTo = objName.RefersTo sObjName = objName.Name 'Check name is scoped to the workbook. If objName.Parent.Name = sWbName Then 'Delete the current name scoped to workbook replacing with worksheet scoped name. objName.Delete ws.Names.Add Name:=sObjName, RefersTo:=sRefersTo End If End If End If Next objName End Sub 

An alternative way is to "hack" the Excel file for 2007 or higher, although it is advisable to take care if you are doing this, and keep a backup of the original:

First save the Excel spreadsheet as an .xlsx or .xlsm file (not binary). rename the file to .zip, then unzip. Go to the xl folder in the zip structure and open workbook.xml in Wordpad or a similar text editor. Named ranges are found in the definedName tags. Local scoping is defined by localSheetId="x" (the sheet IDs can be found by pressing Alt-F11 in Excel, with the spreadsheet open, to get to the VBA window, and then looking at the Project pane). Hidden ranges are defined by hidden="1", so just delete the hidden="1" to unhide, for example.

Now rezip the folder structure, taking care to maintain the integrity of the folder structure, and rename back to .xlsx or .xlsm.

This is probably not the best solution if you need to change the scope of or hide/unhide a large number of defined ranges, though it works fine for making one or two small tweaks.

These answers were helpful in solving a similar issue while trying to define a named range with Workbook scope. The "ah-HA!" for me is to use the Names Collection which is relative to the whole Workbook! This may be restating the obvious to many, but it wasn't clearly stated in my research, so I share for other's with similar questions.

' Local / Worksheet only scope Worksheets("Sheet2").Names.Add Name:="a_test_rng1", RefersTo:=Range("A1:A4") ' Global / Workbook scope ThisWorkbook.Names.Add Name:="a_test_rng2", RefersTo:=Range("B1:b4") 

If you look at your list of names when Sheet2 is active, both ranges are there, but switch to any other sheet, and "a_test_rng1" is not present.

Now I can happily generate a named range in my code with what ever scope I deem appropriate. No need mess around with the name manager or a plug in.

Aside, the name manager in Excel Mac 2011 is a mess, but I did discover that while there are no column labels to tell you what you're looking at while viewing your list of named ranges, if there is a sheet listed beside the name, that name is scoped to worksheet / local. See screenshot attached.

Full credit to this article for putting together the pieces.

create the new name from scratch and delete the old one.

This still needs more refining however works with all simple references, without killing existing local names.

Type GlobalNamesToLocalNames_Type Name As String Sheet As String Ref As String End Type Sub GlobalNamesToLocalNames(Optional Void As Variant) Dim List() As GlobalNamesToLocalNames_Type Dim Count As Long Dim Name As Name Dim Dat() As String Dim X As Long ' count the size For Each Name In ActiveWorkbook.Names Count = Count + 1 Next ReDim List(Count - 1) Count = 0 ' Collecect all name data For Each Name In ActiveWorkbook.Names With List(Count) ' Pick up only the name If InStr(Name.Name, "!") > 0 Then Dat = Split(Name.Name, "!") .Name = Dat(1) Else .Name = Name.Name End If ' pick up the sheet and refer Dat = Split(Name.RefersTo, "!") .Sheet = Mid(Dat(0), 2) .Ref = Dat(1) ' make local sheet name .Name = .Sheet & "!" & .Name End With Count = Count + 1 Next ' Delete all names For Each Name In ActiveWorkbook.Names Name.Delete Next 'rebuild all the names For X = 0 To Count - 1 With List(X) If Left(.Ref, 1) <> "#" Then ActiveWorkbook.Names.Add Name:=.Name, RefersToLocal:="=" & .Sheet & "!" & .Ref End If End With Next End Sub 

For me it works that when I create new Name tag for the same range from the Name Manager it gives me the option to change scope ;) workbook comes as default and can be changed to any of the available sheets.

here's how I promote all worksheet names to global names. YMMV

For Each wsh In ActiveWorkbook.Worksheets For Each n In wsh.Names ' Get unqualified range name Dim s As String s = Split(n.Name, "!")(UBound(Split(n.Name, "!"))) ' Add to "Workbook" scope n.RefersToRange.Name = s ' Remove from "Worksheet" scope Call n.Delete Next n Next wsh 

The code of JS20'07'11 is really incredible simple and direct. One suggestion that I would like to give is to put a exclamation mark in the conditions:

InStr(1, objName.RefersTo, sWsName+"!", vbTextCompare) 

Because this will prevent adding a NamedRange in an incorrect Sheet. Eg: If the NamedRange refers to a Sheet named Plan11 and you have another Sheet named Plan1 the code can do some mess when add the ranges if you don't use the exclamation mark.

UPDATE

A correction: It's best to use a regular expression evaluate the name of the Sheet. A simple function that you can use is the following (adapted by http://blog.malcolmp.com/2010/regular-expressions-excel-add-in, enable Microsoft VBScript Regular Expressions 5.5):

Function xMatch(pattern As String, searchText As String, Optional matchIndex As Integer = 1, Optional ignoreCase As Boolean = True) As String On Error Resume Next Dim RegEx As New RegExp RegEx.Global = True RegEx.MultiLine = True RegEx.pattern = pattern RegEx.ignoreCase = ignoreCase Dim matches As MatchCollection Set matches = RegEx.Execute(searchText) Dim i As Integer i = 1 For Each Match In matches If i = matchIndex Then xMatch = Match.Value End If i = i + 1 Next End Function 

So, You can use something like that:

xMatch("'?" +sWsName + "'?" + "!", objName.RefersTo, 1) <> "" 

instead of

InStr(1, objName.RefersTo, sWsName+"!", vbTextCompare) 

This will cover Plan1 and 'Plan1' (when the range refers to more than one cell) variations

TIP: Avoid Sheet names with single quotes ('), :) .

I added some additional lines of code to JS20'07'11's previous Makro to make sure that the name of the sheet's Named Ranges isn't already a name of the workbook's Named Ranges. Without these lines the already definied workbook scooped Named range is deleted and replaced.

Public Sub RescopeNamedRangesToWorkbookV2() Dim wb As Workbook Dim ws As Worksheet Dim objNameWs As Name Dim objNameWb As Name Dim sWsName As String Dim sWbName As String Dim sRefersTo As String Dim sObjName As String Set wb = ActiveWorkbook Set ws = ActiveSheet sWsName = ws.Name sWbName = wb.Name 'Loop through names in worksheet. For Each objNameWs In ws.Names 'Check name is visble. If objNameWs.Visible = True Then 'Check name refers to a range on the active sheet. If InStr(1, objNameWs.RefersTo, sWsName, vbTextCompare) Then sRefersTo = objNameWs.RefersTo sObjName = objNameWs.Name 'Check name is scoped to the worksheet. If objNameWs.Parent.Name <> sWbName Then 'Delete the current name scoped to worksheet replacing with workbook scoped name. sObjName = Mid(sObjName, InStr(1, sObjName, "!") + 1, Len(sObjName)) 'Check to see if there already is a Named Range with the same Name with the full workbook scope. For Each objNameWb In wb.Names If sObjName = objNameWb.Name Then MsgBox "There is already a Named range with ""Workbook scope"" named """ + sObjName + """. Change either Named Range names or delete one before running this Macro." Exit Sub End If Next objNameWb objNameWs.Delete wb.Names.Add Name:=sObjName, RefersTo:=sRefersTo End If End If End If Next objNameWs End Sub 

Found this at theexceladdict.com

• Select the Named range on your worksheet whose scope you want to change;

• Open the Name Manager (Formulas tab) and select the name;

• Click Delete and OK;

• Click New… and type in the original name back in the Name field;

• Make sure Scope is set to Workbook and click Close.