How to change Named Range Scope

Question

When I create a named range through the Name Manager, I'm given the option of specifying Workbook or [worksheet name] scope. But if then want to change scope, the drop-down is grayed out. Is there a way, in either Name Manager or, preferablly, VBA to change the scope of an existing named range?

For example:

• testName refers to 'sheet1'!A1:B2 with scope Workbook. How would I change that to
• testName refers to 'sheet1'!A1:B2 with 'sheet1' scope?

Answer 1

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

Answer 2

You can download the free Name Manager addin developed by myself and Jan Karel Pieterse from http://www.decisionmodels.com/downloads.htm This enables many name operations that the Excel 2007 Name manager cannot handle, including changing scope of names.

In VBA:

Sub TestName()
Application.Calculation = xlManual
Names("TestName").Delete
Range("Sheet1!$A$1:$B$2").Name = "Sheet1!TestName"
Application.Calculation = xlAutomatic
End Sub

Answer 3

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