To save one worksheet as a separate workbook in Excel VBA, copy it without specifying a destination, then save the newly created workbook with SaveAs. The essential pattern is worksheet.Copy, followed by ActiveWorkbook.SaveAs; a complete example and the important checks are below.
The basic method: copy, then save
In Excel for Microsoft 365 on Windows, the Worksheet.Copy method makes a copy of a sheet. When you call it without a Before or After argument, Excel creates a new workbook containing that sheet. The copied workbook becomes the active workbook, so the macro can save it using SaveAs.
This is different from using SaveAs on the original workbook: saving the original with that method saves the entire workbook, not just the active sheet. The copy-first approach leaves the original workbook open and unchanged while creating a separate file.
For example, if a workbook has sheets named “Data,” “Report,” and “Notes,” copying “Report” this way creates a workbook containing the copied Report sheet. It does not automatically include the other two sheets.
A complete VBA example
This macro copies the worksheet named Report and saves the new workbook in the same folder as the source workbook. It adds the current date to the filename. Save a copy of the source workbook before running any macro: VBA actions cannot be undone with Ctrl+Z.
Option Explicit
Sub SaveReportAsNewWorkbook()
Dim sourceSheet As Worksheet
Dim newBook As Workbook
Dim sourceFolder As String
Dim fullPath As String
' Choose the sheet to export.
Set sourceSheet = ThisWorkbook.Worksheets("Report")
sourceFolder = ThisWorkbook.Path
' A folder is not available until the source workbook has been saved.
If Len(sourceFolder) = 0 Then
MsgBox "Save this workbook to a folder first.", vbExclamation
Exit Sub
End If
' No Before or After argument creates a new workbook.
sourceSheet.Copy
Set newBook = ActiveWorkbook
' Save a dated Excel workbook beside the source file.
fullPath = sourceFolder & Application.PathSeparator & _
"Report_" & Format(Date, "yyyymmdd") & ".xlsx"
newBook.SaveAs Filename:=fullPath, FileFormat:=xlOpenXMLWorkbook
End Sub
To add the macro in Excel for Windows, open the Visual Basic Editor with Alt+F11, choose Insert > Module, and paste the code into the module. Change Report to the exact worksheet tab name, including spaces if it has them. Run the procedure from the editor with F5, or return to Excel and use Developer > Macros. If the Developer tab is hidden, enable it through File > Options > Customize Ribbon.
The example deliberately uses ThisWorkbook to refer to the workbook that contains the macro. It uses ActiveWorkbook only after the copy operation, because the new workbook becomes active at that point. This distinction helps prevent a macro from accidentally saving the wrong workbook.
What the copy includes—and what it may not
A copied worksheet generally retains its cell contents, formulas, formatting, row heights, column widths, and objects such as charts placed on the sheet. It is a copy of the worksheet, not a screenshot or a simple export of displayed values. Features with dependencies elsewhere in the original workbook deserve special attention, however.
For example, a formula on Report might refer to a cell on Data. Once Report is in its own workbook, the formula cannot refer to a local Data sheet if that sheet was not copied. Excel may preserve the reference as a link to the original workbook. Similarly, a chart, named range, validation rule, or PivotTable can depend on data, names, or connections that remain in the source file. Check the exported workbook rather than assuming that every dependency has become self-contained.
- Formulas: Look for references to other sheets or workbooks. Decide whether to retain links, include supporting sheets, or replace formulas with values.
- Charts and PivotTables: Confirm that their data source remains available and that the result displays as expected.
- Names and validation: Review named ranges and drop-down lists that may point to omitted sheets.
- External connections: Check whether the new file depends on a connection, query, or source file that the recipient cannot access.
Choose the correct file format
The example saves as .xlsx using the Excel constant xlOpenXMLWorkbook. This is suitable when the exported workbook does not need to retain VBA macros. An .xlsx file cannot store VBA project code, so Excel may warn that macros will be removed if the new workbook contains code that would be lost.
If the exported workbook must retain VBA, save it as an Excel Macro-Enabled Workbook (.xlsm) with the matching file format constant, xlOpenXMLWorkbookMacroEnabled. The file extension and FileFormat should agree. Saving as .xlsm does not mean every component from the original workbook is automatically packaged: workbook-level code, standard modules, forms, references, and other workbook resources may not be included just because one sheet was copied. Verify which code and features are actually present in the new file.
Use .xlsx for a conventional macro-free deliverable, .xlsm when the deliverable needs VBA, and another format only when the recipient or workflow specifically requires it. CSV is not a replacement for a workbook copy: it stores plain text values for a sheet, not its full formatting, formulas, charts, and workbook features.
Set a different filename or destination
Change the expression assigned to fullPath to control where the workbook is saved. Application.PathSeparator inserts the separator Excel expects for the current operating system, which is more portable than hard-coding a backslash. The destination folder must exist and be writable, and the file name must not contain characters Windows disallows, such as a colon or a question mark.
The sample uses the source workbook’s folder, but a fixed folder can also be used. For example, replace the assignment that builds fullPath with a complete path to an existing folder, then keep the same SaveAs line. If the source workbook has never been saved, ThisWorkbook.Path is empty; the sample stops and asks you to save it first rather than constructing an invalid path.
The date format in the example is year-month-day, which sorts naturally in file listings. Because it contains only the date, running the macro more than once on the same day targets the same filename. Excel normally asks whether to replace an existing file. If you want to preserve each export, add a time component to the filename or choose a unique name before saving. Avoid silently overwriting important output files.
Copying a sheet versus moving or saving the whole file
Use Worksheet.Copy with no destination when the goal is a new workbook containing a copy of one sheet. If you supply a Before or After worksheet, Excel copies the sheet into an existing workbook instead; it does not create a separate workbook through that call. A move is also different from a copy: moving removes the sheet from its original location, while this export pattern leaves the source sheet in place.
Saving the original workbook with Workbook.SaveAs changes the saved file for the whole workbook. That may be right when you want a renamed or reformatted copy of the entire project, but it is not the direct way to create a one-sheet workbook. For an export that includes several selected sheets, Excel’s sheet-copy operation can copy a group of sheets together, but formulas and references among those sheets should still be reviewed.
Common problems and safe checks
If Excel reports that a subscript is out of range, the sheet name in Worksheets("Report") probably does not exactly match a tab name in ThisWorkbook, or the sheet is not in the workbook containing the macro. Check spelling, spaces, and which file contains the code. A chart sheet is not a worksheet; if the target is a chart sheet, its object type and copy logic need to be adjusted.
If SaveAs fails, check that the destination folder exists, the filename is valid, and the workbook is not blocked by permissions or another process. A file already present may prompt for confirmation. If a macro-enabled workbook is being saved as .xlsx, heed the format warning rather than assuming the code will survive. After saving, inspect the new workbook’s title bar and file location to confirm the export went where intended.
Macros may be blocked by Excel’s security settings, or the workbook may open in Protected View. Only enable macros in files and code sources you trust. On a Mac, the Visual Basic Editor and macro features are available in desktop Excel, though menu placement, keyboard keys, and file paths differ; use the Mac Excel menus if the Windows shortcuts do not apply. Excel for the web does not run VBA macros, so run this procedure in the desktop application.
When another export method is better
If the recipient only needs the visible results and not working formulas, you can copy the sheet and paste values into a new workbook, or convert formulas to values in the exported copy. This reduces dependence on source sheets but also removes formula behavior. Keep the source workbook intact and make the value conversion only in the export when you need both an editable source and a static deliverable.
For a one-time task, the Excel interface may be simpler than VBA: right-click the sheet tab, choose Move or Copy, select (new book) in the destination list, and select Create a copy before confirming. This creates a new workbook with the sheet; save that workbook with File > Save As. VBA is more useful when the same export must be repeated, named consistently, or incorporated into a larger workflow.
Frequently Asked Questions
Does Worksheet.Copy create a new workbook automatically?
Yes, when called without a Before or After argument, Worksheet.Copy creates a new workbook containing the copied sheet. The new workbook becomes the active workbook.
How do I copy a sheet without changing the original?
Use the copy method shown above. Do not use a move operation. The source worksheet remains in its original workbook; the new workbook has a separate copy.
Can I save the copied sheet as an XLSM file?
Yes. Use a filename ending in .xlsm and set FileFormat to xlOpenXMLWorkbookMacroEnabled. Confirm that the specific VBA code and dependencies the new workbook needs are actually included.
Why do formulas in the new workbook link back to the old one?
Those formulas may refer to sheets or ranges that were not copied. Excel can retain such references as links to the original workbook. Copy required supporting sheets too, or deliberately replace formulas with values if links are unwanted.
Can this VBA run in Excel for the web?
No. Excel for the web can open and edit many macro-enabled workbooks, but it cannot run VBA macros. Open the workbook in desktop Excel for Windows or Mac to execute the code.
Will the macro overwrite a file with the same name?
The sample uses a date-only filename, so repeated runs on the same date can target the same file. Excel normally displays an overwrite prompt. Choose a new filename, add a time component, or cancel if you need to preserve the existing export.
Related
- Excel Data Validation from a List: Setup and Troubleshooting
- Excel Named Ranges: How to Create and Use Them
- Excel Uppercase to Lowercase Shortcut: What Actually Works
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- Macro Recorder for Mac: Record and Run Excel MacrosIn Excel for Mac, turn on the Developer tab, choose Developer > Record Macro, perform the steps you…
- VBA Sort Range by Two Fields in ExcelTo sort an Excel range by two fields in VBA, add two sort keys in priority order, set…
- Excel VBA Lookup: Find Matching Data with CodeAn Excel VBA lookup searches a range for a value and returns related information, such as a product…
- Excel VBA Text Color: Change Font and Cell Colors EasilyTo change text color in Excel VBA, set a range’s Font.Color property, usually with RGB(red, green, blue). For…
- VBA Code to Sort Data in Excel: Examples and TipsTo sort Excel data with VBA, define the complete data range, add the column or columns to sort…
- VBA Pivot Table Fields: How to Manage ThemIn Excel VBA, a PivotTable’s fields are accessed through its PivotFields collection: set a field’s Orientation to place…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.