Excel VBA: Save One Worksheet as a Separate New File

VBA & macros · Updated November 19, 2026

To save one Excel tab as a separate file with VBA, copy the worksheet into a new workbook, then save that workbook with SaveAs. The macro below prompts for a filename and saves the copied sheet as an .xlsx file, leaving the original workbook open and unchanged.

What “save a tab” means in Excel

An Excel worksheet is a tab inside a workbook; it is not normally a separate file by itself. To make a standalone file, VBA creates a new workbook containing a copy of the chosen worksheet and saves that new workbook. This is different from saving the entire source workbook under another name.

The example below uses the active worksheet, meaning the sheet currently selected when you run the macro. It works in Excel for Microsoft 365 on Windows. It does not copy a chart sheet: select a worksheet tab, rather than a chart tab, before running it.

VBA code to copy and save the active worksheet

This version opens Excel’s Save As dialog, suggests a timestamped filename, and uses the Excel Workbook (.xlsx) format. Choose a folder and confirm the name in the dialog.

Sub SaveActiveSheetAsNewXlsx()
    Dim sourceSheet As Worksheet
    Dim newBook As Workbook
    Dim filePath As Variant

    ' A chart sheet is not a worksheet, so stop if one is active.
    If TypeName(ActiveSheet) <> "Worksheet" Then
        MsgBox "Select a worksheet tab first.", vbExclamation
        Exit Sub
    End If

    Set sourceSheet = ActiveSheet

    ' Ask where to save the new workbook.
    filePath = Application.GetSaveAsFilename( _
        InitialFileName:="Worksheet_" & Format(Now, "yyyymmdd_hhnnss") & ".xlsx", _
        FileFilter:="Excel Workbook (*.xlsx), *.xlsx", _
        Title:="Save copied worksheet as")

    ' Cancel returns False; do not create a copy in that case.
    If VarType(filePath) = vbBoolean Then Exit Sub

    On Error GoTo SaveFailed

    ' Copying one sheet creates a new workbook containing that sheet.
    sourceSheet.Copy
    Set newBook = ActiveWorkbook

    newBook.SaveAs Filename:=CStr(filePath), _
        FileFormat:=xlOpenXMLWorkbook

    MsgBox "Saved the copied worksheet to:" & vbCrLf & CStr(filePath), vbInformation
    Exit Sub

SaveFailed:
    MsgBox "The new workbook could not be saved: " & Err.Description, vbExclamation
End Sub

The timestamp helps avoid accidentally choosing the same suggested filename each time, but it is not a guarantee against every collision. If a file with that name already exists, Excel may ask whether to replace it; read that prompt carefully. If the save fails, the copied workbook may remain open. You can close it without saving if you do not need it.

How to add and run the macro

  1. Save a copy of your source workbook before running any macro. VBA actions cannot be undone with Ctrl+Z.
  2. In Windows Excel, press Alt+F11 to open the Visual Basic Editor. Choose Insert > Module, then paste the code into the module.
  3. Return to Excel, select the worksheet you want to copy, and press Alt+F8. Select SaveActiveSheetAsNewXlsx and choose Run.
  4. Choose a destination and filename in the dialog. Open the resulting file and check its contents before distributing it.

Store the macro in an .xlsm workbook or another macro-enabled location if you want to run it again later. If Excel displays a security warning, only enable macros for a workbook you trust. In Excel for Mac, the Visual Basic Editor and macro dialogs are available, but keyboard shortcuts can depend on the keyboard and function-key settings; use the Mac menu or Excel’s shortcut settings if Alt+F11 or Alt+F8 does not apply. The save dialog may also look different.

What gets copied to the new workbook

sourceSheet.Copy copies the selected worksheet to a new workbook. It generally carries over the sheet’s cells, formatting, formulas, tables, and many objects on the sheet, such as charts and shapes. The source sheet remains in its original workbook. This makes the method useful for creating a separate report, form, or shareable tab without first removing the other tabs from the source file.

A worksheet is not always self-contained, however. A formula that refers to another sheet in the original workbook may become an external link to that workbook, because the referenced sheet was not copied. Named ranges, charts, validation rules, and other features can also rely on workbook-level information or supporting sheets. After saving, inspect formulas and objects that depend on anything outside the copied tab. If you need those dependencies to work locally, copy the required sheets too, or adjust the references deliberately.

Copying a worksheet is also different from copying only its visible values. Formulas remain formulas unless you replace them with values, and hidden rows, columns, or objects may still be present. Check for sensitive or unintended content before sending the new file to someone else.

Choose the right file format

The example saves as .xlsx using xlOpenXMLWorkbook. This is the standard workbook format for ordinary Excel content, but it cannot store VBA macros. The macro that runs the save operation is in the source workbook; it is not automatically included as a standard VBA module in the new workbook.

If the copied sheet itself contains worksheet event code and you need VBA in the destination, use a macro-enabled format instead: give the file an .xlsm extension and change the file-format argument to xlOpenXMLWorkbookMacroEnabled. A copied sheet’s code is not a substitute for checking the destination project: standard modules and other source-workbook code are not simply bundled by copying one tab. Inspect the new workbook’s VBA project and confirm that the code you require is there. Excel may show a format warning if the content and extension do not match; do not dismiss it without understanding the consequence.

Use .xlsx when the recipient needs the sheet’s ordinary workbook content but not macros. Use .xlsm only when macro functionality is genuinely required and the recipient can safely use a macro-enabled file.

Formulas, values, and links: decide what the recipient needs

Many people asking how to save a tab actually want a static snapshot rather than a working copy. The macro above preserves formulas, so results can change when inputs change, and formulas that refer outside the copied worksheet may depend on the original file. It does not automatically convert formulas into their displayed results.

For a snapshot, one option is to copy the new sheet’s used cells and paste values over them before saving. Do this only if losing formulas is intentional, and consider whether formatting, column widths, print settings, and objects also need to be retained. Another option is to keep the formulas and include all the supporting sheets they require. A third is to edit formulas so they refer to an approved source or to values stored within the new workbook. In each case, open the saved file separately and verify that the displayed results match your intended output.

Filename and destination considerations

The dialog lets you choose both the folder and filename, which is more flexible than hard-coding a path such as a particular user’s Documents folder. A fixed path can fail on another computer, when a folder is renamed, or when the workbook is run from a different account. If you later adapt the code to build filenames automatically, ensure the name has a valid extension and does not contain characters Windows forbids in filenames, such as a colon or a slash.

  • Use a clear filename that identifies the report or period, rather than relying only on “Sheet1.”
  • Check the destination folder and whether a file with the same name already exists.
  • When using a shared or cloud-synced folder, make sure Excel has finished saving before moving or renaming the file.
  • Keep the original workbook until you have confirmed the separate file opens and contains the right sheet.

Common problems and how to fix them

  • The macro is not listed. Confirm the code is in a standard module, the procedure is a public Sub without arguments, and macros are enabled for a trusted workbook.
  • Excel says the active sheet is not a worksheet. Select a worksheet tab. The active item may be a chart sheet, which cannot be assigned to the code’s Worksheet variable.
  • The new file contains broken or external links. Review formulas and named references that point to sheets or data not included in the copy. Copy dependencies or replace those references as needed.
  • The file opens with a format or macro warning. Make sure the extension matches the chosen file format. XLSX does not preserve VBA; choose XLSM when macro content is required.
  • The save dialog is cancelled or the save fails. Cancelling is a normal exit. For an error, check that the folder is writable, the filename is valid, and the file is not blocked by permissions or another application.

When to use a different method

For a one-time task, Excel’s interface may be quicker: right-click the worksheet tab, choose Move or Copy, select (new book), tick Create a copy, and click OK. Then use File > Save As to save the new workbook. The macro is most helpful when you repeat the task, want a predictable process, or need to build the action into a larger workflow.

Do not use SaveCopyAs as a direct replacement for this approach. That method saves a copy of an entire workbook file; it does not extract one worksheet into a new workbook. Likewise, hiding or deleting other tabs before saving is a riskier way to make a single-sheet file, because it modifies the source workbook unless you carefully manage another copy. The worksheet-copy method keeps the original workbook intact while creating a separate destination workbook.

Frequently Asked Questions

Can VBA save a worksheet as a new workbook?
Yes. Use Worksheet.Copy to create a new workbook containing that sheet, then use the new workbook’s SaveAs method to choose its filename and format.

Does copying a tab change the original workbook?
No. Copying the worksheet to a new workbook leaves the original sheet in place. The macro changes the new workbook when it saves it, not the source workbook.

Can I save the copied sheet as a CSV file?
CSV stores plain text data from a sheet, not the full Excel worksheet experience. It does not retain formatting, charts, multiple tabs, or formulas as formulas. If you need an Excel workbook with formatting and functionality, use .xlsx or, when appropriate, .xlsm.

Will formulas still work in the separate file?
Formulas that use only content included in the new workbook can continue to work. References to sheets or ranges left behind in the source workbook may become external links, so inspect dependent formulas after copying.

Can I run the same macro on a sheet chosen by name?
Yes. Instead of assigning ActiveSheet, VBA can refer to a worksheet by name, for example ThisWorkbook.Worksheets("Summary"). The name must match a worksheet in that workbook, and the code should handle the case where the sheet is missing.

Related

Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.

More in VBA & macros

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.