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

User · Answer

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

User · Answer

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  lt  gt  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

User · Answer

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

User · Answer

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

User · Answer

create the new name from scratch and delete the old one

User · Answer

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

User · Answer

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

User · Answer

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   lt  gt       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

User · Answer

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        gt  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  amp       amp   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   lt  gt      Then             ActiveWorkbook Names Add Name   Name  RefersToLocal       amp   Sheet  amp       amp   Ref          End If       End With    Next End Sub

User · Answer

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  lt  gt  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

User · Answer

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

User · Answer

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

[vba] How to change Named Range Scope

Examples related to vba

Examples related to excel