VBA & Macro Problems

Manage Named Ranges When Copying Excel Worksheets with VBA

Steven ParkerSteven Parker Nov 22, 2026 868 views

Question details

Use worksheet-scoped named ranges to make copied Excel worksheets easier to manage and reduce conflicts with workbook-wide names.

Manage Named Ranges When Copying Excel Worksheets with VBA
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.
Before you start

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.

Solution 1Recommended

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.

1
Create the local name

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

2
Run the copy macro

Run CopySheetWithLocalName from the Visual Basic editor or through Developer > Macros. Excel adds a copy after the last worksheet.

3
Verify the copied name

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.

4
Reference the local name

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 worksheet-scoped name before copying with VBA
Check existing workbook names: A local name can take precedence over a workbook-scoped name with the same spelling on its worksheet. Check formulas and Name Manager after making changes.
Free Microsoft Office alternative

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.

Use named ranges to make spreadsheet formulas easier to manageOpen and edit Microsoft Excel XLSX filesNavigate a familiar, easy-to-use spreadsheet interfaceUse a free, lightweight office suite for everyday work
microsoft office alternative - wps office

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.