How to change Named Range Scope

excel, vba

Solution

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

Problem

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?

Original source