How to change Named Range Scope
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?
'sheet1'!A1:B2with scope Workbook. How would I change that to
'sheet1'!A1:B2with 'sheet1' scope?
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.
Sub TestName() Application.Calculation = xlManual Names("TestName").Delete Range("Sheet1!$A$1:$B$2").Name = "Sheet1!TestName" Application.Calculation = xlAutomatic End Sub