VBA Code to Save Selected Excel Worksheets as a New Workbook
Question details
The user wants a VBA macro to copy only the currently selected worksheets from a large template and save them as a new workbook with a dynamically generated filename.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Extracting specific worksheets from an Excel file and automatically saving them as a new file, using cell values and the current date to name the new file.
- Observed behavior
- The user needs the exact VBA syntax to efficiently copy the ActiveWindow.SelectedSheets, assign the dynamic filename, save the new file, and properly close it.
Before running any VBA code, ensure you have enabled the Developer tab in your spreadsheet application and saved your main template as a Macro-Enabled Workbook (.xlsm).
Use VBA SaveAs Method to Export Selected Sheets
This recommended macro copies the selected sheets to a new workbook and uses the SaveAs method to explicitly assign a dynamic filename based on a cell value and the current date.
Using the SaveAs method gives you full control over the file format and ensures the newly created workbook is safely saved to the exact path of your original template.
Press the 'Alt + F11' keys on your keyboard to open the Visual Basic for Applications (VBA) editor.
In the VBA editor, click on 'Insert' in the top menu, then select 'Module' to create a blank script window.
Paste the following code: Sub SaveSelectedSheets() Dim folderPath As String, fileName As String folderPath = ThisWorkbook.Path fileName = ActiveWindow.SelectedSheets(1).Range("A1").Value & "_" & Format(Date, "yyyymmdd") & ".xlsx" Application.ScreenUpdating = False ActiveWindow.SelectedSheets.Copy ActiveWorkbook.SaveAs Filename:=folderPath & "\" & fileName, FileFormat:=xlOpenXMLWorkbook ActiveWorkbook.Close SaveChanges:=False Application.ScreenUpdating = True End Sub
Select the sheets you want to export in your main workbook, return to the VBA editor, and press 'F5' to run the code.
Save and Close Using the Workbook.Close Statement
Alternatively, you can save the newly created workbook by passing the Filename argument directly to the ActiveWorkbook.Close command.
Run Excel VBA Macros Smoothly in WPS Office
WPS Office provides robust support for VBA macros, allowing you to run, edit, and create automation scripts just like in Microsoft Excel. You can seamlessly use the exact same VBA code to save selected worksheets as a new workbook without any modifications.
- 1. Download WPS Office: Install WPS Office and open your macro-enabled spreadsheet.
- 2. Access Developer Tools: Navigate to the 'Tools' tab on the ribbon and click on 'Developer' to reveal the macro features.
- 3. Open the VBA Editor: Click 'VBA Editor' to paste your worksheet extraction code into a new module.
- 4. Execute Your Automation: Select the tabs you wish to copy and run the macro to instantly generate your new Excel file.
Frequently Asked Questions
How do I select multiple worksheets before running the VBA macro?
You can select multiple discrete sheets by holding down the 'Ctrl' key and clicking the individual sheet tabs at the bottom of the window. To select a continuous range, click the first sheet tab, hold the 'Shift' key, and click the last sheet tab.
Why does my macro fail when saving the new workbook?
This usually happens if the dynamically generated filename contains invalid characters (such as slashes, colons, asterisks, or question marks) derived from the cell value. It can also occur if the target folder path does not exist or lacks write permissions.
Will formulas in the selected sheets still work in the newly saved workbook?
If the formulas only reference cells within the copied sheets, they will work perfectly. However, if they reference sheets that were not copied to the new workbook, they will automatically convert into external links pointing back to your original template.
