VBA & Macro Problems

How to Change Excel Named Ranges from Workbook to Worksheet Scope

Amos Gikunda Oct 12, 2026 870 views

Question details

The user needs to convert existing workbook-level named ranges to worksheet-level scope without breaking existing formulas or references.

How to Change Excel Named Ranges from Workbook to Worksheet Scope

Product: Microsoft Excel
Device & OS: not provided
Scenario: Copying an Excel worksheet containing workbook-scoped named ranges creates unwanted duplicate names with suffixes.
Observed behavior: The goal is to cleanly adjust the scope of the named ranges to specific worksheets to maintain accurate references and avoid suffix duplication when duplicating sheets.

Before you start

Always create a backup copy of your workbook before running VBA scripts that delete and recreate named ranges, as it may temporarily disrupt active formulas if the script encounters an error.

Solution 1Recommended

Use a VBA Macro to Convert Named Range Scope

Running a short VBA procedure allows you to automate the process of deleting workbook-scoped names and recreating them at the worksheet level.

Since spreadsheet software does not provide a built-in UI button to directly change the scope of an existing named range once it is created, using VBA is the most efficient method.

The macro will loop through the workbook names, capture their formulas (RefersTo paths), delete the global name, and add it back explicitly assigned to the local worksheet's Names collection.

Open the VBA Editor

Press Alt + F11 on your keyboard to open the Visual Basic for Applications (VBA) editor.

Insert a New Module

Click 'Insert' from the top menu and select 'Module' to create a new blank script window.

Enter the VBA Code

Write or paste a VBA script that loops through the ActiveWorkbook.Names collection. For each name, the script should store the name string and RefersTo property, delete the global name using .Delete, and recreate it using the target worksheet's Names.Add method.

Run the Macro

Press F5 or click the Run button to execute the code. Once completed, close the VBA editor and verify your formulas to ensure all references remained intact.

Testing Required: Test this macro on a copy of your file first. Deleting names briefly breaks formulas until the macro successfully recreates them, so testing ensures your specific workbook structure is compatible.

Advanced Spreadsheet Management

Manage Named Ranges and Macros Easily with WPS Office

WPS Spreadsheet provides robust support for named ranges and VBA macros, allowing you to organize your data and execute automation scripts safely and efficiently.

1. Open your file in WPS Spreadsheet: Launch WPS Spreadsheet and open your macro-enabled (.xlsm) workbook.
2. Access Developer Tools: Navigate to the Developer tab on the ribbon to open the VBA editor.
3. Run your Scope Conversion Script: Paste your VBA code into the module and run it to instantly switch workbook scopes to worksheet scopes.
4. Verify with the Name Manager: Go to the Formulas tab and open the Name Manager to visually confirm that the scopes have been successfully updated to the worksheet level.

Full support for VBA macros to automate complex scope changesSeamless compatibility with Microsoft Excel .xlsx and .xlsm formatsIntuitive Name Manager interface for easy auditing of range scopesLightweight and fast performance even with highly complex workbooks

microsoft office alternative - wps office

Frequently Asked Questions

Can I change the named range scope without using VBA?

No, there is no direct feature in the Name Manager to edit the scope of an existing named range. You must either delete and manually recreate the name with the new scope or use a VBA macro to automate the process.

What happens if I don't change the scope and copy the sheet?

If you copy a worksheet that contains workbook-scoped named ranges, the software will automatically create duplicate named ranges with numerical suffixes (like 'Name_1', 'Name_2') to avoid conflicts within the workbook.

Why did my formulas return a #NAME? error after running the macro?

This error occurs if the macro deleted the named range but failed to recreate it properly, or if the spelling of the name or sheet in the VBA script didn't match. Always run VBA scripts on a backup file to prevent data loss.

How do I check the current scope of my named ranges?

Go to the Formulas tab on the ribbon and click on 'Name Manager'. Look at the 'Scope' column in the dialog box to see whether each range applies to the entire 'Workbook' or to a specific 'Worksheet'.

Excel & Data Problems