How to change Named Range Scope

Question

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?

Stephen

Answer 1

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

Answer 2

create the new name from scratch and delete the old one.