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?

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?


You can download the free Name Manager addin developed by myself and Jan Karel Pieterse from This enables many name operations that the Excel 2007 Name manager cannot handle, including changing scope of names.


Sub TestName()
Application.Calculation = xlManual
Range("Sheet1!$A$1:$B$2").Name = "Sheet1!TestName"
Application.Calculation = xlAutomatic
End Sub