How to Copy Named Ranges from One Excel Workbook to Another Using VBA
Question details
The user needs to transfer workbook-level named ranges between two Excel files using a VBA script.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Upgrading spreadsheets or moving data that relies heavily on named ranges and defined references across different workbooks.
- Observed behavior
- The user wants a programmatic solution to copy named ranges, ensuring their exact names and 'RefersTo' formulas are precisely duplicated in the new destination workbook.
Ensure both your source and destination workbooks are open in Excel before running the macro, and verify that the destination workbook contains identically named sheets if your ranges use sheet-specific references.
Use a VBA Macro to Copy Workbook-Level Named Ranges
Run a customized VBA script to loop through your source workbook's Names collection and add them automatically to your destination workbook.
Using VBA is the most efficient way to transfer multiple named ranges without having to copy entire worksheets manually. This macro reads the name and the reference formula (RefersTo) from the source file and recreates them exactly in the destination file.
Launch Excel and open both the source workbook (containing your named ranges) and the destination workbook (where you want them copied).
Press Alt + F11 on your keyboard to launch the Visual Basic for Applications (VBA) editor window.
In the top menu, click 'Insert' and select 'Module' to create a blank script window.
Type or paste the macro code. Define your variables (Dim srcWb As Workbook, destWb As Workbook, nm As Name), use 'Set srcWb = Workbooks("SourceWorkbookName.xlsx")' and 'Set destWb = Workbooks("DestinationWorkbookName.xlsx")', then loop with 'For Each nm In srcWb.Names : destWb.Names.Add Name:=nm.Name, RefersTo:=nm.RefersTo : Next nm'. Replace the placeholder workbook names with your actual exact file names.
Press F5 or click the green 'Run' button in the toolbar. The macro will execute, transferring all named ranges instantly.
Manage Macros and Named Ranges Effortlessly in WPS Office
WPS Spreadsheet fully supports VBA macros, allowing you to run automated scripts for transferring named ranges, managing complex formulas, and boosting your productivity seamlessly.
- 1. Open WPS Spreadsheet: Launch WPS Office and load both your source and destination workbooks.
- 2. Open the VBA Editor: Go to the 'Developer' tab on the top ribbon and click on 'Visual Basic', or use the Alt+F11 shortcut.
- 3. Insert the Script: Click 'Insert' > 'Module', paste your named-range copying VBA script, and modify the placeholder workbook names to match your open files.
- 4. Run the Macro: Click 'Run' to seamlessly copy your named ranges within the WPS Spreadsheet environment.
Frequently Asked Questions
Why do my copied named ranges show a #REF! error?
This usually happens if the named range refers to a worksheet that doesn't exist in the destination workbook. Ensure that the sheet names in the destination file exactly match those in the source file before running the macro.
Can I copy worksheet-level named ranges using this macro?
The standard VBA macro provided copies workbook-level named ranges. To copy worksheet-level names, you need to modify the VBA script to loop through the specific 'Worksheet.Names' collection instead of the 'Workbook.Names' collection.
Do I need to save the destination workbook as a macro-enabled file (.xlsm)?
If you paste the VBA code into the destination workbook and wish to keep it for future use, you must save the file as a Macro-Enabled Workbook (.xlsm). If you only run the code once and do not need to save the script itself, standard .xlsx format is fine.
Is there a non-VBA method to copy named ranges?
Yes, you can copy an entire worksheet that contains the named ranges from the source workbook to the new workbook. Excel automatically brings the associated named ranges along with the copied sheet. However, for bulk copying across many sheets, VBA remains the fastest method.
