Export One Excel Worksheet to a Separate Workbook with VBA
Question details
Export only one selected worksheet from an Excel workbook to a separate workbook by using VBA.

- Product
- Microsoft Excel VBA
- Device & OS
- not provided
- Scenario
- The existing macro loops through every worksheet and creates multiple workbooks, but the user needs to export one chosen sheet to a named destination folder.
- Observed behavior
- The current VBA code exports all worksheets instead of copying and saving only the selected worksheet.
Confirm that the destination folder already exists and that the workbook is saved as a macro-enabled file if you need to keep the VBA code. Decide whether the macro should export the active sheet or a worksheet identified by its name or position.
Copy and save only the active worksheet with VBA
Use this method when the worksheet to export is the currently selected sheet. It replaces any loop that processes every worksheet.
Remove or comment out the For Each loop that cycles through Worksheets. The macro below assigns ActiveSheet to a worksheet variable, copies that one sheet into a new workbook, saves it, and closes the exported file.
In Excel, press Alt+F11, then select Insert > Module to create a standard VBA module.
Paste this code into the module: Sub ExportActiveSheet()\n Dim xWs As Worksheet\n Dim newWb As Workbook\n Dim saveFolder As String\n Dim fileName As String\n\n On Error GoTo CleanUp\n Application.ScreenUpdating = False\n\n Set xWs = ActiveSheet\n saveFolder = "C:\Exports\\"\n fileName = xWs.Name & ".xlsx"\n\n xWs.Copy\n Set newWb = ActiveWorkbook\n newWb.SaveAs Filename:=saveFolder & fileName, FileFormat:=xlOpenXMLWorkbook\n newWb.Close SaveChanges:=False\n\nCleanUp:\n Application.ScreenUpdating = True\n If Err.Number <> 0 Then\n MsgBox "The worksheet was not exported: " & Err.Description, vbExclamation\n End If\nEnd Sub
Replace C:\Exports\ with your existing folder path. Keep FileFormat:=xlOpenXMLWorkbook for an .xlsx output, or use FileFormat:=xlOpenXMLWorkbookMacroEnabled and an .xlsm extension when the copied worksheet must retain macros.
Return to Excel, click the worksheet you want to export, then press Alt+F8, select ExportActiveSheet, and click Run.

Export a specific worksheet by name or worksheet number
Use this option when the macro should always export the same worksheet, regardless of which tab is active.
Manage and export individual worksheets with WPS Spreadsheet
WPS Spreadsheet provides a familiar worksheet workspace for selecting, organizing, copying, and saving individual sheets. You can use it to prepare worksheet-specific files and work with common Excel workbook formats before sharing them.
- 1. Open the workbook in WPS Spreadsheet: Launch WPS Office, choose Spreadsheet, and open the Excel workbook containing the worksheet you need.
- 2. Select the worksheet tab: Click the worksheet tab at the bottom of the workbook to make the required sheet active.
- 3. Copy the worksheet content: Select the used range on the worksheet, then use Home > Copy or press Ctrl+C.
- 4. Create a workbook for the export: Choose Menu > New > Spreadsheet, select cell A1, and use Home > Paste or press Ctrl+V.
- 5. Save in an Excel-compatible format: Choose Menu > Save As, select Excel Workbook (*.xlsx), enter a file name, choose the destination folder, and click Save.

Frequently Asked Questions
Why does my VBA macro export every worksheet?
The code likely contains a loop such as For Each xWs In ThisWorkbook.Worksheets. Remove that loop and assign one worksheet variable, such as Set xWs = ActiveSheet or Set xWs = ThisWorkbook.Worksheets("Report").
How do I export the first worksheet instead of the active worksheet?
Use Set xWs = ThisWorkbook.Worksheets(1) before xWs.Copy. This exports the first worksheet tab in the workbook regardless of the active selection.
Which SaveAs format should I use for an exported Excel worksheet?
Use FileFormat:=xlOpenXMLWorkbook with an .xlsx file name for a standard workbook. Use FileFormat:=xlOpenXMLWorkbookMacroEnabled with an .xlsm file name only when the exported workbook must retain VBA macros.
What happens if the destination folder does not exist?
Workbook.SaveAs will fail if the folder path is invalid or missing. Create the folder first, or update the saveFolder value to an existing location such as C:\Exports\.
Can I export a worksheet without changing the source workbook?
Yes. The xWs.Copy method creates a new workbook containing the copied sheet. Saving and closing that new workbook does not modify the original workbook.




