Change the scope of a Defined Name in Excel 2007

exl

Board Regular
Joined
Feb 4, 2010
Messages
153
Hi,

I have a sheet with 20 column and wanted to use the column headers as names, so I opted the Create name from selection option. It was a breeze to create so many names so quickly, but the problem is the scope of the names was set to Workbook.

Is there any way to change the scope from workbook to a particular worksheet?
 

Smitty

MrExcel MVP
Joined
May 15, 2003
Messages
29,536
Welcome to the Board!

As far as I recall, you have to define the scope when you create a named range.

Excel MVP Jan Karel Pieterse's Name Manager Add-In might have a workaround though.

HTH,
 

JoeMo

MrExcel MVP
Joined
May 26, 2009
Messages
16,666
Hi,

I have a sheet with 20 column and wanted to use the column headers as names, so I opted the Create name from selection option. It was a breeze to create so many names so quickly, but the problem is the scope of the names was set to Workbook.

Is there any way to change the scope from workbook to a particular worksheet?
I think Smitty is correct. You have to declare the scope of a named range when you create it. And, Jan Karel Pieterse's Name Manager can be used to make the change.

As an alternative, the code below can be used to change any global name that refers to a range on the active sheet to a local name scoped to that sheet. The global name is removed, as well.
Code:
Sub ChangeNameScope()
Dim nm As Name, locNam
Dim wSh As Worksheet
Set wSh = ActiveSheet
For Each nm In ActiveWorkbook.Names
    If InStr(nm.RefersTo, wSh.Name) > 0 Then
        On Error Resume Next
        If Not nm.RefersToRange Is Nothing Then
            With nm.RefersToRange
                locNam = nm.Name
                'remove global name
                ActiveWorkbook.Names(nm.Name).Delete
                'Add local name
                .Parent.Names.Add Name:=locNam, RefersTo:="=" & .Address
            End With
        End If
    On Error GoTo 0
    End If
Next nm
End Sub
 
Last edited:

Marcelo Branco

MrExcel MVP
Joined
Aug 23, 2010
Messages
16,317
Another alternative

Change the scope from Workbook to Sheet1 of an array of named ranges
You can adjust the Sheet and the named ranges

Code:
Sub ChangeScope()
    Dim myArray As Variant, elem As Variant
    Dim rngAux As Range, nameAux As String
    Dim ws As Worksheet
    
    Application.Calculation = xlManual
    Set ws = ThisWorkbook.Sheets("Sheet1") '<--Adjust sheetname
    
    myArray = Array("Header1", "Header2", "Header3", "Header4") '<-- Adjust the named ranges
    
    For Each elem In myArray
        On Error Resume Next
        With ThisWorkbook.Names(elem)
            Set rngAux = .RefersToRange
            nameAux = "'" & ws.Name & "'!" & .Name
            .Delete
        End With
        
        If Err = 0 Then ws.Range(rngAux.Address).Name = nameAux
        On Error GoTo 0
    Next elem
    
     Application.Calculation = xlAutomatic
End Sub
M.
 

Jim885

Well-known Member
Joined
Jul 8, 2012
Messages
625
On the FORMULA tab, you can click Name Manager or click "Define Name" depending on your need. You can then chose how to edit what you want from the message box that appears. Sounds to me that you need the Define Name. The message box that appears will allow you to change the scope of the Ranges from workbook to worksheets and allow cell range changes too.
 
Last edited:

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
42,251
Office Version
365
Platform
Windows
The message box that appears will allow you to change the scope of the Ranges from workbook to worksheets...
Unfortunately, not if you have already created the names the way the OP has described.
 

exl

Board Regular
Joined
Feb 4, 2010
Messages
153
Thanks to all for the solutions, but they havent quite worked the way I wanted to:

1. @Smitty, @JoeMo, @Marcelo Branco : All your solutions work, but they only change the scope to the active sheet, in which the data is present i.e. Sheet 1, I want the name to scoped to the sheet of my choice.

2. @Jim885 : I know about the solution you talking about, but if I have lots of names then it is a very time consuming process to define each name.
 

JoeMo

MrExcel MVP
Joined
May 26, 2009
Messages
16,666
Thanks to all for the solutions, but they havent quite worked the way I wanted to:

1. @Smitty, @JoeMo, @Marcelo Branco : All your solutions work, but they only change the scope to the active sheet, in which the data is present i.e. Sheet 1, I want the name to scoped to the sheet of my choice.

2. @Jim885 : I know about the solution you talking about, but if I have lots of names then it is a very time consuming process to define each name.
Have you considered making the sheet of your choice the active sheet?
 

exl

Board Regular
Joined
Feb 4, 2010
Messages
153
Have you considered making the sheet of your choice the active sheet?
The active sheet is my base data, and my dashboard is in a different sheet, hence the active sheet (or the sheet containing the names) cannot be the same.

I am consolidating various dashboards into one single file, hence require the base data to be in that single file.
 

JoeMo

MrExcel MVP
Joined
May 26, 2009
Messages
16,666
The active sheet is my base data, and my dashboard is in a different sheet, hence the active sheet (or the sheet containing the names) cannot be the same.

I am consolidating various dashboards into one single file, hence require the base data to be in that single file.
In the code I posted there is a statement:
Set wSh = ActiveSheet

Change Activesheet to Sheets("your sheet name between these quotes")

Marcelo's code also can be easily altered to conform to the sheet of your choice.
 

Forum statistics

Threads
1,082,257
Messages
5,364,074
Members
400,778
Latest member
Canadian Sal

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top