VBA & Macro Problems

How to Save Groups of Excel Sheets as Separate Files Using VBA

Phi Hung Vo Oct 21, 2026 868 views

Question details

The user needs a VBA macro to select a specific group of worksheets from a main workbook and save them together as a new, standalone file.

How to Save Groups of Excel Sheets as Separate Files Using VBA

Product: Excel
Device & OS: not provided
Scenario: A user has a large workbook where specific worksheets are related (e.g., one sheet contains lookups for several others) and wants to extract and export this group of tabs together as a separate file.
Observed behavior: The user is looking for the correct VBA code syntax to execute the grouping, copying, and saving process to an independent directory without manually extracting tabs.

Before you start

Create a backup of your original workbook before testing the macro, as copied sheets might retain external formula references pointing back to the source file.

Solution 1Recommended

Use the Worksheets.Copy Method with an Array

Execute a VBA macro that targets an array of specific sheet names, copies them to a new workbook, and automatically saves it to your desired folder.

This method is ideal when you have a specific, fixed set of tabs that always need to be exported together. By copying them simultaneously in an array, any internal formulas linking those specific sheets are successfully preserved in the new workbook.

Open the VBA Editor

Press Alt + F11 in your open Excel workbook to launch the Microsoft Visual Basic for Applications window.

Insert a New Module

In the top menu, click on 'Insert' and select 'Module' to create a blank script window where you can write your code.

Input the VBA Code

Enter the following command to copy the sheets: Worksheets(Array("Sheet1", "Sheet2", "Sheet3")).Copy. On the next lines, define the save process by typing ActiveWorkbook.SaveAs "C:\Path\Group.xlsx" followed by ActiveWorkbook.Close.

Customize the Script

Replace 'Sheet1', 'Sheet2', and 'Sheet3' with the exact names of the tabs you want to extract. Change 'C:\Path\Group.xlsx' to the actual folder path and filename you want to create.

Run the Macro

Press F5 or click the 'Run' icon in the toolbar. The macro will immediately group your defined sheets and save them to the specified location.

Use the Worksheets.Copy Method with an Array

Handling Formula References: If the extracted sheets contain formulas referencing tabs that were left behind in the main workbook, the newly saved file will convert those formulas into external links pointing to the original document.

Advanced Spreadsheet Automation

Run VBA Macros Seamlessly in WPS Office

WPS Spreadsheet provides powerful native support for VBA macros, allowing you to easily automate tasks like splitting, copying, and grouping worksheets into separate files.

1. Open Your Workbook in WPS: Launch WPS Office and open your master spreadsheet file containing the tabs you want to export.
2. Access Developer Tools: Navigate to the 'Developer' tab on the ribbon menu and click on 'Visual Basic', or simply press Alt + F11 on your keyboard.
3. Run Your Macro: Insert a module, paste your worksheet array-copy VBA script, and click 'Run' to generate your new, grouped file instantly.

Fully compatible with Microsoft Excel formats, including .xlsx, .xls, and macro-enabled .xlsm filesRobust native VBA environment to run advanced scripts and automate repetitive tasksLightweight, fast software that runs flawlessly on Windows, Mac, and Linux

microsoft office alternative - wps office

Frequently Asked Questions

How do I save every single sheet in my workbook as a separate file?

Instead of specifying an array, you can use a loop in your VBA script: 'For Each ws In ThisWorkbook.Worksheets'. Inside the loop, run 'ws.Copy', then 'ActiveWorkbook.SaveAs', and finally 'ActiveWorkbook.Close' to save each tab individually.

Why do I get a 'Subscript out of range' error when running the array macro?

This error occurs if the sheet names specified in your Array("Name1", "Name2") do not match the exact tab names in your workbook. Double-check your workbook for spelling mistakes or hidden spaces in the tab names.

How can I automatically add today's date to the newly saved file's name?

You can dynamically name the file by concatenating the VBA Date function into your SaveAs path. For example: ActiveWorkbook.SaveAs "C:\Path\Group_" & Format(Date, "yyyy-mm-dd") & ".xlsx".

Excel & Data Problems