Manage Named Ranges When Copying Excel Worksheets with VBA
Question details
Use worksheet-scoped named ranges to make copied Excel worksheets easier to manage and reduce conflicts with workbook-wide names.

- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- A user copies worksheets with VBA and wants to manage named ranges without relying on duplicate workbook-wide names.
- Observed behavior
- Each worksheet can have its own local name, which can be referenced using the worksheet name.
Open Formulas > Name Manager and check the Scope and Refers to columns for the names used by the worksheet. Choose a local name that does not already exist on the source sheet before running the VBA example.
Create a worksheet-scoped name before copying with VBA
Use a local name when each copied worksheet should have its own version of a named range.
A worksheet-scoped name belongs to one sheet rather than the entire workbook. Copying the sheet also copies its local names, so you generally do not need to rename them individually.
This approach does not automatically convert or remove existing workbook-scoped names. Review those names separately, especially if copying to another workbook.
In Excel, open Developer > Visual Basic, insert a module, and add this macro. Replace Sheet Name, RangeName, and $A$1 with your worksheet, an unused local name, and the intended range: Sub CopySheetWithLocalName() Dim ws As Worksheet Set ws = ThisWorkbook.Worksheets("Sheet Name") ws.Names.Add Name:="RangeName", RefersTo:="='" & Replace(ws.Name, "'", "''") & "'!$A$1" ws.Copy After:=ThisWorkbook.Worksheets(ThisWorkbook.Worksheets.Count) End Sub
Run CopySheetWithLocalName from the Visual Basic editor or through Developer > Macros. Excel adds a copy after the last worksheet.
Open Formulas > Name Manager and check the Scope and Refers to columns for RangeName. Confirm that the copied worksheet has its own local name and that it refers to the intended range.
In a worksheet formula, qualify the name with its sheet, for example ='Sheet Name'!RangeName. Use the copied worksheet's actual name when referring to its local name.

Create a local name in Excel Name Manager
Use the Excel interface to set up a worksheet-scoped name before copying a sheet manually or with VBA.
Work with named ranges in WPS Spreadsheets
WPS Office is a free, lightweight alternative with a familiar spreadsheet interface and Microsoft Office format compatibility, making it easier to move existing files into a new workflow. Excel VBA behavior and macro support may differ, so test any automation separately.

Frequently Asked Questions
How do I tell whether an Excel name is local or workbook-wide?
Open Formulas > Name Manager and look at the Scope column. A worksheet name indicates local scope; Workbook indicates workbook-wide scope.
Do I need to rename a local range on every copied worksheet?
Generally, no. A copied worksheet retains its own local names, so separate sheets can use the same local name. Check Formulas > Name Manager to confirm each name refers to the intended range.
How do I refer to a local name from another worksheet?
Include the sheet name in the formula, for example ='Sheet Name'!RangeName. If the sheet name contains an apostrophe, double it inside the quoted sheet name.
Will creating a local name remove an existing workbook-scoped name?
No. Creating a local name does not remove or convert a workbook-scoped name. Review both in Formulas > Name Manager and update affected formulas as needed.




