Change the scope of a Defined Name in Excel 2007

[exl]

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]

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]

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.

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

 

[Marcelo Branco]

Another alternative

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

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]

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.

 

[Peter_SSs]

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]

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]

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]

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]

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.