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?

 

[exl]

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.

It did not work, my names were on Sheet 1 scoped to Workbook, I tried to scope it to Sheet2, following code i used:

 

[JoeMo]

It did not work, my names were on Sheet 1 scoped to Workbook, I tried to scope it to Sheet2, following code i used:

Try this (change the names of Sheet1 and Sheet2 to suit):

Sub ChangeNameScope()
Dim nm As Name, locNam
Dim wSh As Worksheet
Set wSh = Sheets("Sheet1")
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
                Sheets("Sheet2").Names.Add Name:=locNam, RefersTo:="=" & .Address
            End With
        End If
    On Error GoTo 0
    End If
Next nm
End Sub

 

[exl]

Try this (change the names of Sheet1 and Sheet2 to suit):

Sub ChangeNameScope()
Dim nm As Name, locNam
Dim wSh As Worksheet
Set wSh = Sheets("Sheet1")
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
                Sheets("Sheet2").Names.Add Name:=locNam, RefersTo:="=" & .Address
            End With
        End If
    On Error GoTo 0
    End If
Next nm
End Sub

No joy. The code runs, but nothing happens, the names remain scoped to workbook. Names & data in Sheet 1, i changed Sheet1 to Sheet 2 in the code..

 

[JoeMo]

No joy. The code runs, but nothing happens, the names remain scoped to workbook. Names & data in Sheet 1, i changed Sheet1 to Sheet 2 in the code..

I have to tell you I'm confused as to which sheet is which. If you changed Sheet1 to sheet2, then that's back to the code I posted originally unless you changed sheet2 to something else.

Please confirm or modify my understanding at this point:
Sheet1 has some named ranges that are global in scope.
You would like to convert these names to local scope and have them apply to the same ranges, but in Sheet2.

The code I posted last is intended to accomplish this.

 

[exl]

I have to tell you I'm confused as to which sheet is which. If you changed Sheet1 to sheet2, then that's back to the code I posted originally unless you changed sheet2 to something else.

Please confirm or modify my understanding at this point:
Sheet1 has some named ranges that are global in scope.
You would like to convert these names to local scope and have them apply to the same ranges, but in Sheet2.

The code I posted last is intended to accomplish this.

Dear Joe,

It workss. Now tell me, where do I change the code in future to refer to any other worksheet.

I also have a general query, is the problem I am facing unusual or universal and has Microsoft brought in some rectifications in the latest version of excel.

Thanks for your help Joe, I really appreciate it.

 

[JoeMo]

Dear Joe,

It workss. Now tell me, where do I change the code in future to refer to any other worksheet.

I also have a general query, is the problem I am facing unusual or universal and has Microsoft brought in some rectifications in the latest version of excel.

Thanks for your help Joe, I really appreciate it.

There are two places in the code that contain the sheet references. One as Sheets("Sheet1") and the second as Sheets("Sheet2"). the portion inside the quote marks (") is the name of the sheet, that is what is on the sheet's tab.
I do not use the latest version of Excel, but I suspect you must still choose global or local when you first define a name for a range, and can only change it via VBA in that version too. Of course, if you are only dealing with a few names, you can simply delete the global version and then enter it as local via the Name Manager.

 

[MarkJWales]

The steps below do not require programming and should work as long as other sheets do not have cells that refer to the sheet you are modifying:
1) Save your spreadsheet (always a good idea - thing sometimes go wrong)
2) Copy the sheet: Right-click on the tab, select "Move or Copy", Click "Create a copy", then click OK
3) Delete the original sheet: Right click on the tab and select "Delete"
4) Rename the new sheet with the original name: Double click on the tab and change the name
5) Go to the Formulas tab then click "Name Manager".
6) Delete any names that have "#REF!" in their "Value" or "Refers to"
7) Make sure the scope of all remaining Names is "Workbook"
8) Test the spreadsheet to verify that everything works (it should work but always good to check)
I hope this helps.

 

[JenniferMurphy]

The steps below do not require programming and should work as long as other sheets do not have cells that refer to the sheet you are modifying:
1) Save your spreadsheet (always a good idea - thing sometimes go wrong)
2) Copy the sheet: Right-click on the tab, select "Move or Copy", Click "Create a copy", then click OK
3) Delete the original sheet: Right click on the tab and select "Delete"
4) Rename the new sheet with the original name: Double click on the tab and change the name
5) Go to the Formulas tab then click "Name Manager".
6) Delete any names that have "#REF!" in their "Value" or "Refers to"
7) Make sure the scope of all remaining Names is "Workbook"
8) Test the spreadsheet to verify that everything works (it should work but always good to check)
I hope this helps.

Mark,

Thank you! Thank you! Thank you! You are a genius!!! This method is so simple and so easy. If Microsoft had people like you writing the code for their office products, we would have many fewer perplexing problems to dissect around here.

This is great!