How to change scope (Workbook to Sheet) of Formula Name after duplicated with Sheet?

S

SunnySchindler

New Member
Joined
Feb 9, 2021
Messages
9
Office Version
  1. 365
Platform
  1. Windows
At Name creation, Excel defaults to Workbook; which was fine when intended with only one sheet.
Now after finishing all the formulas, I have new need which requires Sheet duplicated to same workbook.
What I need is help on, preferably non-programmatically, is all Names to remain same for maintenance purpose; but the duplicated Names are suffixed with Numbers; I believe the only way is to re-scope all Names from Global to where Sheet .

I have modified following code contributed by someone else who needs the other way round; all Names to be scoped Workbook.

My code to-date shown below
VBA Code: 
Option Explicit
Sub ChangeLocalNameAndOrScope()
'Jul 10, 2015 https://www.mrexcel.com/board/members/joemo.118363/
'I wrote this several years ago and have not used it recently so I would suggest trying it on a copy of your workbook first.
'EDIT: If its not obvious, just enter the name you want w/o any sheet qualifier to get back to global (workbook-level) scope.
'Programmatically change a sheet-level range name and/or scope to a new name and/or scope

'GFIN Sunny modify 230817 - to
' include global names
' suggest new name
Dim ProcName, Message, Title, Default, MyValue, Wsh As Variant
ProcName = "ChangeLocalNameAndOrScope" 'Module Name

With ActiveWorkbook.ActiveSheet
    Wsh = .Name
    Stop
    Message = "Enter a value between 1 and 3"    ' Set prompt.
    Title = "InputBox Demo" & ProcName   ' Set title.
    Default = Wsh   ' Set default.
    ' Display dialog box at position 100, 100.
    MyValue = InputBox(Message, Title, Default, 100, 100)
    'GFIN Sunny modify 230817
    Stop
   
    Dim nm As Name, Ans As Integer, newNm As String
    Debug.Print ActiveWorkbook.Name
    Stop
    For Each nm In ActiveWorkbook.Names
   
    'GFIN Sunny modify 230817
    Message = "Enter new name - inculde 'Sheet1'! for a local name"    ' Set prompt.
    Title = "InputBox Demo" & ProcName    ' Set title.


    'GFIN Sunny modify 230817
   
        If nm.Name Like "*!*" Then 'It is sheet level
'            Ans = MsgBox(nm.Name & " is a worksheet level name - do you want to change it?", vbYesNo)
'            If Ans = vbYes Then
'
'    '            newNm = InputBox("Enter new name - inculde 'Sheet1'! for a local name")
'                If newNm = "" Then Exit Sub
'                Range(nm.RefersTo).Name = newNm
'                nm.Delete
'            End If
        Else
            'Jul 10, 2015  Ans = MsgBox(nm.Name & " is a Workbook level name - do you want to change it?", vbYesNo)
   
   
   ' Stop
            Default = Wsh & nm.Name ' Set default.
            ' Display dialog box at position 100, 100.
            MyValue = InputBox(Message, Title, Default, 100, 100)
            newNm = MyValue
            If Ans = vbYes Then
                newNm = InputBox("Enter new name - inculde 'Sheet1'! for a local name")
                If newNm = "" Then Exit Sub
                Range(nm.RefersTo).Name = newNm
                nm.Delete
            End If
        End If
    Next nm

End With
End Sub
 

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.
Sort by date Sort by votes
SunnySchindler said:
At Name creation, Excel defaults to Workbook; which was fine when intended with only one sheet.
Now after finishing all the formulas, I have new need which requires Sheet duplicated to same workbook.
What I need is help on, preferably non-programmatically, is all Names to remain same for maintenance purpose; but the duplicated Names are suffixed with Numbers; I believe the only way is to re-scope all Names from Global to where Sheet .

I have modified following code contributed by someone else who needs the other way round; all Names to be scoped Workbook.

My code to-date shown below
VBA Code: 
Option Explicit
Sub ChangeLocalNameAndOrScope()
'Jul 10, 2015 https://www.mrexcel.com/board/members/joemo.118363/
'I wrote this several years ago and have not used it recently so I would suggest trying it on a copy of your workbook first.
'EDIT: If its not obvious, just enter the name you want w/o any sheet qualifier to get back to global (workbook-level) scope.
'Programmatically change a sheet-level range name and/or scope to a new name and/or scope

'GFIN Sunny modify 230817 - to
' include global names
' suggest new name
Dim ProcName, Message, Title, Default, MyValue, Wsh As Variant
ProcName = "ChangeLocalNameAndOrScope" 'Module Name

With ActiveWorkbook.ActiveSheet
    Wsh = .Name
    Stop
    Message = "Enter a value between 1 and 3"    ' Set prompt.
    Title = "InputBox Demo" & ProcName   ' Set title.
    Default = Wsh   ' Set default.
    ' Display dialog box at position 100, 100.
    MyValue = InputBox(Message, Title, Default, 100, 100)
    'GFIN Sunny modify 230817
    Stop
  
    Dim nm As Name, Ans As Integer, newNm As String
    Debug.Print ActiveWorkbook.Name
    Stop
    For Each nm In ActiveWorkbook.Names
  
    'GFIN Sunny modify 230817
    Message = "Enter new name - inculde 'Sheet1'! for a local name"    ' Set prompt.
    Title = "InputBox Demo" & ProcName    ' Set title.


    'GFIN Sunny modify 230817
  
        If nm.Name Like "*!*" Then 'It is sheet level
'            Ans = MsgBox(nm.Name & " is a worksheet level name - do you want to change it?", vbYesNo)
'            If Ans = vbYes Then
'
'    '            newNm = InputBox("Enter new name - inculde 'Sheet1'! for a local name")
'                If newNm = "" Then Exit Sub
'                Range(nm.RefersTo).Name = newNm
'                nm.Delete
'            End If
        Else
            'Jul 10, 2015  Ans = MsgBox(nm.Name & " is a Workbook level name - do you want to change it?", vbYesNo)
  
  
   ' Stop
            Default = Wsh & nm.Name ' Set default.
            ' Display dialog box at position 100, 100.
            MyValue = InputBox(Message, Title, Default, 100, 100)
            newNm = MyValue
            If Ans = vbYes Then
                newNm = InputBox("Enter new name - inculde 'Sheet1'! for a local name")
                If newNm = "" Then Exit Sub
                Range(nm.RefersTo).Name = newNm
                nm.Delete
            End If
        End If
    Next nm

End With
End Sub
Click to expand...
Would using the sheet name as a prefix to the named range name be workable? See here

I would create a routine that duplicates the sheet and create the named ranges for the new sheet based upon the named ranges in the duplicated sheet at the same time.
 
Upvote 0
SunnySchindler said:
At Name creation, Excel defaults to Workbook; which was fine when intended with only one sheet.
Now after finishing all the formulas, I have new need which requires Sheet duplicated to same workbook.
What I need is help on, preferably non-programmatically, is all Names to remain same for maintenance purpose; but the duplicated Names are suffixed with Numbers; I believe the only way is to re-scope all Names from Global to where Sheet .

I have modified following code contributed by someone else who needs the other way round; all Names to be scoped Workbook.

My code to-date shown below
VBA Code: 
Option Explicit
Sub ChangeLocalNameAndOrScope()
'Jul 10, 2015 https://www.mrexcel.com/board/members/joemo.118363/
'I wrote this several years ago and have not used it recently so I would suggest trying it on a copy of your workbook first.
'EDIT: If its not obvious, just enter the name you want w/o any sheet qualifier to get back to global (workbook-level) scope.
'Programmatically change a sheet-level range name and/or scope to a new name and/or scope

'GFIN Sunny modify 230817 - to
' include global names
' suggest new name
Dim ProcName, Message, Title, Default, MyValue, Wsh As Variant
ProcName = "ChangeLocalNameAndOrScope" 'Module Name

With ActiveWorkbook.ActiveSheet
    Wsh = .Name
    Stop
    Message = "Enter a value between 1 and 3"    ' Set prompt.
    Title = "InputBox Demo" & ProcName   ' Set title.
    Default = Wsh   ' Set default.
    ' Display dialog box at position 100, 100.
    MyValue = InputBox(Message, Title, Default, 100, 100)
    'GFIN Sunny modify 230817
    Stop
  
    Dim nm As Name, Ans As Integer, newNm As String
    Debug.Print ActiveWorkbook.Name
    Stop
    For Each nm In ActiveWorkbook.Names
  
    'GFIN Sunny modify 230817
    Message = "Enter new name - inculde 'Sheet1'! for a local name"    ' Set prompt.
    Title = "InputBox Demo" & ProcName    ' Set title.


    'GFIN Sunny modify 230817
  
        If nm.Name Like "*!*" Then 'It is sheet level
'            Ans = MsgBox(nm.Name & " is a worksheet level name - do you want to change it?", vbYesNo)
'            If Ans = vbYes Then
'
'    '            newNm = InputBox("Enter new name - inculde 'Sheet1'! for a local name")
'                If newNm = "" Then Exit Sub
'                Range(nm.RefersTo).Name = newNm
'                nm.Delete
'            End If
        Else
            'Jul 10, 2015  Ans = MsgBox(nm.Name & " is a Workbook level name - do you want to change it?", vbYesNo)
  
  
   ' Stop
            Default = Wsh & nm.Name ' Set default.
            ' Display dialog box at position 100, 100.
            MyValue = InputBox(Message, Title, Default, 100, 100)
            newNm = MyValue
            If Ans = vbYes Then
                newNm = InputBox("Enter new name - inculde 'Sheet1'! for a local name")
                If newNm = "" Then Exit Sub
                Range(nm.RefersTo).Name = newNm
                nm.Delete
            End If
        End If
    Next nm

End With
End Sub
Click to expand...
Reading this may be useful : Link
 
Upvote 0
Herakles said:
Reading this may be useful : Link
Click to expand...
Please correct me if wrong, as someone who have been doing Excel VBA, I need to stay alert that cloning has different consequences for Names as attempted to explain below.



Sheet Clone/DuplicateName ScopeName Cloned (without extra steps)
VBAlocal/Worksheetsame
Named Formulaeglobal/Workbooksuffixed


I hastily forgot above and because the need of such calculation is one-off (at the time) to take extra step to change default to Sheet (even though there is only one Sheet).


Now after finishing all the formulas, I have new need which requires Sheet duplicated to same workbook.
What I need is help on, preferably non-programmatically, is all Names to remain same for maintenance purpose; but the duplicated Names are suffixed with Numbers; I believe the only way is to re-scope each Name to its referred Sheet.
 
Upvote 0
Herakles said:
Would using the sheet name as a prefix to the named range name be workable? See here

I would create a routine that duplicates the sheet and create the named ranges for the new sheet based upon the named ranges in the duplicated sheet at the same time.
Click to expand...
Please correct me if wrong, as someone who have been doing Excel VBA, I need to stay alert that cloning has different consequences for Names as attempted to explain below.



Sheet Clone/DuplicateName ScopeName Cloned (without extra steps)
VBAlocal/Worksheetsame
Named Formulaeglobal/Workbooksuffixed


I hastily forgot above and because the need of such calculation is one-off (at the time) to take extra step to change default to Sheet (even though there is only one Sheet).


Now after finishing all the formulas, I have new need which requires Sheet duplicated to same workbook.
What I need is help on, preferably non-programmatically, is all Names to remain same for maintenance purpose; but the duplicated Names are suffixed with Numbers; I believe the only way is to re-scope each Name to its referred Sheet.
 
Upvote 0
Herakles said:
Would using the sheet name as a prefix to the named range name be workable? See here

I would create a routine that duplicates the sheet and create the named ranges for the new sheet based upon the named ranges in the duplicated sheet at the same time.
Click to expand...
Thanks for the suggestion, but I prefer "retrofitting" repositioned cells (duplicated sheet) with a Clickable Solution.
 
Upvote 0
SunnySchindler said:
At Name creation, Excel defaults to Workbook; which was fine when intended with only one sheet.
Now after finishing all the formulas, I have new need which requires Sheet duplicated to same workbook.
What I need is help on, preferably non-programmatically, is all Names to remain same for maintenance purpose; but the duplicated Names are suffixed with Numbers; I believe the only way is to re-scope all Names from Global to where Sheet .

I have modified following code contributed by someone else who needs the other way round; all Names to be scoped Workbook.

My code to-date shown below
VBA Code: 
Option Explicit
Sub ChangeLocalNameAndOrScope()
'Jul 10, 2015 https://www.mrexcel.com/board/members/joemo.118363/
'I wrote this several years ago and have not used it recently so I would suggest trying it on a copy of your workbook first.
'EDIT: If its not obvious, just enter the name you want w/o any sheet qualifier to get back to global (workbook-level) scope.
'Programmatically change a sheet-level range name and/or scope to a new name and/or scope

'GFIN Sunny modify 230817 - to
' include global names
' suggest new name
Dim ProcName, Message, Title, Default, MyValue, Wsh As Variant
ProcName = "ChangeLocalNameAndOrScope" 'Module Name

With ActiveWorkbook.ActiveSheet
    Wsh = .Name
    Stop
    Message = "Enter a value between 1 and 3"    ' Set prompt.
    Title = "InputBox Demo" & ProcName   ' Set title.
    Default = Wsh   ' Set default.
    ' Display dialog box at position 100, 100.
    MyValue = InputBox(Message, Title, Default, 100, 100)
    'GFIN Sunny modify 230817
    Stop
  
    Dim nm As Name, Ans As Integer, newNm As String
    Debug.Print ActiveWorkbook.Name
    Stop
    For Each nm In ActiveWorkbook.Names
  
    'GFIN Sunny modify 230817
    Message = "Enter new name - inculde 'Sheet1'! for a local name"    ' Set prompt.
    Title = "InputBox Demo" & ProcName    ' Set title.


    'GFIN Sunny modify 230817
  
        If nm.Name Like "*!*" Then 'It is sheet level
'            Ans = MsgBox(nm.Name & " is a worksheet level name - do you want to change it?", vbYesNo)
'            If Ans = vbYes Then
'
'    '            newNm = InputBox("Enter new name - inculde 'Sheet1'! for a local name")
'                If newNm = "" Then Exit Sub
'                Range(nm.RefersTo).Name = newNm
'                nm.Delete
'            End If
        Else
            'Jul 10, 2015  Ans = MsgBox(nm.Name & " is a Workbook level name - do you want to change it?", vbYesNo)
  
  
   ' Stop
            Default = Wsh & nm.Name ' Set default.
            ' Display dialog box at position 100, 100.
            MyValue = InputBox(Message, Title, Default, 100, 100)
            newNm = MyValue
            If Ans = vbYes Then
                newNm = InputBox("Enter new name - inculde 'Sheet1'! for a local name")
                If newNm = "" Then Exit Sub
                Range(nm.RefersTo).Name = newNm
                nm.Delete
            End If
        End If
    Next nm

End With
End Sub
Click to expand...
Following code is to run in a Target Sheet to be pasted with Source Sheet which needs Named Ranges to demote Scope from Global to This Sheet.

VBA Code: 
Option Explicit
'GFIN Sunny to retrofit Named Range created Workbook by default
'No existing VBA found
'Following code is to run in a Target Sheet to be pasted with Source Sheet which needs Named Ranges to demote Scope from Global to This Sheet.
'Tested with single Named Range created Workbook Scope
'Creation Date: 2023-08-22
Dim Message, Title, Default, MyValue As Variant, ProcName As String
Dim WBThis As Workbook, WshThis As Worksheet, WshThis_Name As String, WBThis_Names As Names
Dim nm As Name
Sub Main()
    ProcName = "Main" 'Module Name
    Debug.Print ProcName & " " & " Runs"
    Set WBThis = ActiveWorkbook: Set WshThis = ActiveSheet:  Set WBThis_Names = WBThis.Names:  WshThis_Name = WshThis.Name
ChangeLocalNameAndOrScope
    ProcName = "Main" 'Module Name
    Debug.Print ProcName & " " & " Exits"
End Sub
Sub DeleteNamedRangesInWorksheet()
ProcName = "DeleteNamedRangesInWorksheet" 'Module Name
'https://www.spreadsheetweb.com/delete-named-range-excel-vba/
    Debug.Print ProcName & " " & " Runs"
    'by Ilker | Nov 12, 2018 | Excel Macros, Excel Tips & Tricks
    With WshThis
        For Each nm In .Names
            'If nm.RefersToRange.Parent.Name = "Sheet1" Then nm.Delete
            Debug.Print ProcName & " " & .Name & " " & nm.RefersToRange.Parent.Name
            If nm.RefersToRange.Parent.Name = .Name Then
                nm.Delete
                Debug.Print ProcName & " " & .Name & " Deleted " & nm.RefersToRange.Parent.Name
            End If
        Next nm
    End With
    Debug.Print ProcName & " " & " Exits"
End Sub
Sub ChangeLocalNameAndOrScope()
'Jul 10, 2015 https://www.mrexcel.com/board/members/joemo.118363/
'I wrote this several years ago and have not used it recently so I would suggest trying it on a copy of your workbook first.
'EDIT: If its not obvious, just enter the name you want w/o any sheet qualifier to get back to global (workbook-level) scope.
'Programmatically change a sheet-level range name and/or scope to a new name and/or scope

'GFIN Sunny modify 230817 - to
    ProcName = "ChangeLocalNameAndOrScope" 'Module Name
    
    With WshThis ' ActiveWorkbook.ActiveSheet
        
        Dim Ans As Integer, newNm As String
            Debug.Print ActiveWorkbook.Name
        For Each nm In ActiveWorkbook.Names
        Debug.Print nm.Name & " " & nm.RefersTo
     
            If nm.Name Like "*!*" Then 'It is sheet level
            Else

Dim Remember_Range As Range, Remember_name As String
Set Remember_Range = nm.RefersToRange
Remember_name = nm.Name
                    nm.Delete
AddNamedRangeToThisSheet Remember_Range, Remember_name

            End If
        Next nm
    
    End With
End Sub
Sub AddNamedRangeToThisSheet(myNamedRange As Range, myRangeName As String)
ProcName = "AddNamedRangeToThisSheet" 'Module Name
    Debug.Print ProcName & " " & " Runs"
'https://stackoverflow.com/questions/3255478/put-excel-vba-code-in-module-or-sheet
'2023 13 years ago
    '// Vars
    With WshThis
        '// Declare vars
        Dim myRangeNameR1C1 As String
    
        '// identify ranges
        With WshThis.Names
        .Add Name:=myRangeName, RefersTo:=myNamedRange
          Debug.Print ProcName & " " & WBThis.Name & " added " '  & nm
        End With
    End With
        Debug.Print ProcName & " " & " Exits"
End Sub
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Solved
VBA copy template sheet to new monthly workbook with daily sheets tabname and header A2:J2 named as "dd mm yyyy" + autosave filename predefined
Replies
4
Views
809
mumps
mumps
R
  • Solved
Incorporating my VBA code (that saves all sheets from an excel workbook to CSV) into a looping VBA program (just have myself stumped!)
Replies
3
Views
1K
Anthony47
A
R
  • Question Question
VBA copy from single sheet with cell values as reference and paste to another workbook's multiple tabs following the tabs name
Replies
4
Views
3K
GWteB
GWteB
R
  • Question Question
Rename New Workbook Based on Condition from other Sheet
Replies
1
Views
779
R4yHunt
R
J
  • Question Question
Excel VBA to save a sheet as a PDF and name it after the workbook name AND sheet name
Replies
2
Views
6K
jkalpus
J

Forum statistics

Threads
1,224,818
Messages
6,181,151
Members
453,021
Latest member
Justyna P

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top