VBA & Macro Problems

Export One Excel Worksheet to a Separate Workbook with VBA

Steve KSteve K Nov 12, 2026 869 views

Question details

Export only one selected worksheet from an Excel workbook to a separate workbook by using VBA.

Export One Excel Worksheet to a Separate Workbook with 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.
Before you start

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.

Solution 1Recommended

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.

1
Open the Visual Basic Editor

In Excel, press Alt+F11, then select Insert > Module to create a standard VBA module.

2
Paste the single-sheet export macro

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

3
Set the destination folder and file format

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.

4
Run the macro from the target sheet

Return to Excel, click the worksheet you want to export, then press Alt+F8, select ExportActiveSheet, and click Run.

Copy and save only the active worksheet with VBA
Only the selected worksheet is exported: Because the macro uses Set xWs = ActiveSheet, it does not loop through or export the other worksheets in the source workbook.
Work with spreadsheets more efficiently

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. 1. Open the workbook in WPS Spreadsheet: Launch WPS Office, choose Spreadsheet, and open the Excel workbook containing the worksheet you need.
  2. 2. Select the worksheet tab: Click the worksheet tab at the bottom of the workbook to make the required sheet active.
  3. 3. Copy the worksheet content: Select the used range on the worksheet, then use Home > Copy or press Ctrl+C.
  4. 4. Create a workbook for the export: Choose Menu > New > Spreadsheet, select cell A1, and use Home > Paste or press Ctrl+V.
  5. 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.
Select and copy a single worksheet for worksheet-specific exportsOpen and edit Microsoft Excel .xlsx and .xls filesUse a familiar spreadsheet interface for sheet tabs, formulas, and formattingLightweight Office suite for everyday workbook management
microsoft office alternative - wps office

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.