In Excel for Microsoft 365 on Windows, VBA can export a worksheet by copying it into a new workbook and saving that workbook as a separate file. The macro below exports a sheet named Report as an .xlsx file, while leaving the original workbook open and unchanged.
What exporting a worksheet means
An Excel worksheet is one tab in a workbook; a workbook is the entire file. Exporting a worksheet usually means creating a second workbook that contains a copy of the selected sheet, then saving that new workbook to a chosen location. The original workbook remains the source, so its other sheets are not included in the exported file.
This is different from moving a sheet, which removes it from the original workbook, and from saving a copy of the entire workbook, which includes all its sheets. It is also different from exporting a sheet as a PDF or text file: those formats preserve a view or plain data, not a working Excel worksheet with its cell structure.
Before running any VBA macro, save a copy of the workbook. Macro actions generally cannot be undone with Ctrl+Z, so a backup is the safest way to recover if the macro saves the wrong sheet or file.
VBA macro to export one worksheet
Press Alt+F11 in Excel for Windows to open the Visual Basic Editor. Choose Insert > Module, paste this code into the module, and change Report if your worksheet has a different tab name. Run the macro with the cursor inside it by pressing F5, or run it from Excel’s Developer > Macros list.
Sub ExportReportWorksheet()
Dim sourceSheet As Worksheet
Dim exportBook As Workbook
Dim targetPath As Variant
Dim suggestedName As String
' Use the workbook that contains this macro.
Set sourceSheet = ThisWorkbook.Worksheets("Report")
' Suggest the source folder when the workbook has been saved.
suggestedName = "Report.xlsx"
If Len(ThisWorkbook.Path) > 0 Then
suggestedName = ThisWorkbook.Path & Application.PathSeparator & suggestedName
End If
' Let the user choose the destination and file name.
targetPath = Application.GetSaveAsFilename( _
InitialFileName:=suggestedName, _
FileFilter:="Excel Workbook (*.xlsx), *.xlsx", _
Title:="Export worksheet")
' GetSaveAsFilename returns False if the user cancels.
If VarType(targetPath) = vbBoolean Then Exit Sub
' Copy the sheet into a new workbook, then save that workbook.
sourceSheet.Copy
Set exportBook = ActiveWorkbook
exportBook.SaveAs Filename:=CStr(targetPath), FileFormat:=xlOpenXMLWorkbook
exportBook.Close SaveChanges:=False
End Sub
The macro opens a Save As dialog, so you can change the suggested name or folder. Choose a destination and keep the .xlsx extension for a standard macro-free Excel workbook. If you cancel the dialog, the macro exits without creating the export. The source sheet is copied rather than moved.
How the macro works
ThisWorkbook refers to the workbook containing the macro, and Worksheets("Report") identifies the sheet by its tab name. Tab names must match exactly, including spaces. For example, a tab named Monthly Report requires that exact text in the code. If the name is wrong, VBA stops with a “subscript out of range” error.
The worksheet’s Copy method, used without a destination argument, creates a new workbook containing that sheet. Excel makes the new workbook active, which is why the next line assigns ActiveWorkbook to exportBook. The SaveAs method then saves that workbook using Excel’s standard .xlsx file format. Finally, the new workbook closes; the original remains open.
Put the macro in the workbook you want to export from if you plan to use ThisWorkbook as shown. If the code is stored in another workbook, such as Personal.xlsb, ThisWorkbook points to that code-storage workbook instead. In that case, explicitly identify the source workbook or use a carefully selected active workbook; relying on whichever workbook happens to be active can export the wrong sheet.
Choose a format that matches the purpose
An .xlsx export is suitable for sharing an ordinary worksheet, including its cell values, formulas, formatting, and many sheet-level features. It does not retain VBA macros. The workbook containing the macro can remain an .xlsm file while the exported copy is .xlsx; those are separate files with separate formats.
- .xlsx: A common choice for a worksheet that does not need embedded VBA code.
- .xlsm: Use a macro-enabled workbook format if the exported workbook must retain VBA. The save format in the example would need to be changed to
xlOpenXMLWorkbookMacroEnabled, and the chosen filename should end in.xlsm. - .csv: Use this for plain, comma-separated text data when formulas and formatting are not required. CSV saves text from a sheet, not a full Excel worksheet, and it does not preserve multiple sheets or workbook features.
- .pdf: Use this when the goal is a fixed, printable or view-only page rather than editable cell data. PDF export follows print and page layout settings.
Copying a worksheet can also carry formulas that refer to other sheets or workbooks. If the copied sheet contains a formula linked to a sheet that was not copied, Excel may preserve the relationship as a reference to the original workbook. Review formulas in the export if it must work independently. A copied sheet’s external links, named ranges, charts, and other objects may also need checking in context.
Export formulas or values only
The example copies the sheet as it is, so formulas remain formulas. That is useful when recipients should be able to recalculate or inspect the logic, but those formulas may depend on cells or sheets that are not part of the export. If recipients only need the displayed results, convert the copied sheet’s used cells to values before saving. Do this to the new workbook, not the source, and check whether you also need to keep number formats, column widths, charts, or other layout elements.
A values-only conversion is not a universal improvement. It removes formulas and therefore removes the ability to update results when inputs change. It can also leave a static report whose figures no longer reflect the source workbook. For a recurring export, decide whether the output is meant to be a live spreadsheet or a frozen snapshot, and make that choice explicit to anyone receiving it.
There is another important distinction: copying one worksheet does not automatically make every dependency self-contained. A formula can still refer to a cell on a missing sheet, and a chart may have a source range elsewhere in the original workbook. If the exported file is intended to stand alone, test it after saving: reopen it, inspect key formulas, and confirm that charts and values show what you expect.
Exporting several worksheets or files
For a small number of sheets, copy each required sheet into a new workbook and save it. Excel can also copy multiple selected sheets together, but doing so preserves links among those copied sheets while references to sheets left behind may still point outside the export. This makes the choice of which sheets to include a data-integrity decision, not just a cosmetic one.
To create one workbook per sheet automatically, a loop can iterate through the worksheets, copy each one, and save each resulting workbook. A production macro should also decide what to do with hidden sheets, chart sheets, existing files, invalid filename characters, and sheets with duplicate or very long names. It should not silently overwrite important exports. Start with a test workbook and a small set of sheets before automating a large batch.
In particular, avoid building a destination filename directly from a worksheet name without checking it. Windows filenames cannot contain characters such as a colon, question mark, quotation mark, or slash. A sheet name containing one of those characters can cause a save failure. A batch routine should replace or remove invalid characters, create the destination folder if appropriate, and report which files succeeded or failed.
Common errors and practical checks
- “Subscript out of range”: The worksheet name in the code does not match a tab in the workbook referenced by
ThisWorkbook. Check spelling, spaces, and which workbook contains the macro. - The wrong workbook is used:
ThisWorkbookmeans the workbook containing the code;ActiveWorkbookmeans the workbook currently active in Excel. They are not interchangeable. - The save dialog is canceled: The macro should stop cleanly, as the example does. If you modify the dialog logic, preserve a check for its Boolean
Falsereturn value. - The file appears to be missing: Check the folder and filename selected in the dialog. If the source workbook has never been saved, there is no source folder to suggest, so the dialog starts with a simple filename.
- Excel asks about macros: Saving a workbook containing VBA as .xlsx cannot preserve that VBA. Use .xlsm when the output needs its own macros, or continue with .xlsx if it should be macro-free.
- Formulas show errors or links: The copied sheet may rely on content outside itself. Review its formulas and linked objects, and include necessary supporting sheets or convert appropriate results to values.
When replacing an existing file, read Excel’s confirmation prompt carefully. The sample does not suppress alerts, so Excel can ask before overwriting. Keeping that prompt is safer than turning alerts off without adding error handling and a clear overwrite policy.
Macro security and platform differences
VBA runs in the desktop Excel application, not Excel for the web. Excel for the web can open and edit many workbooks that contain macros, but it cannot run VBA macros. Use desktop Excel to run this export procedure. Excel for Mac supports VBA, although dialog and file-path behavior can differ from Windows; test the procedure on the target Mac and Excel version rather than assuming every Windows-specific file operation will behave identically.
To keep the macro in the source workbook, save that workbook as an .xlsm file. Excel may require you to enable macros when opening it, depending on security settings and where the file came from. Do not enable macros in an untrusted workbook just to export a sheet. Use code you understand, keep a backup, and follow your organization’s macro policies. Excel’s Trust Center settings affect whether macros run, but lowering security globally is not a safe workaround for an individual file.
Alternatives to VBA
If this is a one-time task, use Excel’s built-in sheet-copy feature instead of writing code: right-click the worksheet tab, choose Move or Copy, select (new book) in the “To book” list, check Create a copy, and choose OK. Then use File > Save As to save the new workbook. This is also a useful way to verify what a VBA export should produce.
For a read-only or print-ready handoff, use File > Export or Save As and choose PDF, then check the print preview and page layout. For plain data interchange, save a copy as CSV, remembering that it represents one sheet’s text data rather than a formatted workbook. For repeated, rule-based reporting, VBA can save time, but a manual copy may be easier to audit when the process is occasional or the output needs a human review.
Frequently Asked Questions
Can VBA export one worksheet without changing the original workbook?
Yes. The example uses the worksheet’s Copy method to create a separate workbook, then saves and closes that copy. It does not delete or move the source sheet.
How do I export a worksheet as an .xlsx file?
Copy the worksheet into a new workbook and save the new workbook with FileFormat:=xlOpenXMLWorkbook. Use a filename ending in .xlsx and remember that .xlsx does not retain VBA macros in the exported workbook.
Can the macro save the exported file automatically without asking?
Yes, the save dialog can be replaced with a fixed path in VBA. Before doing so, decide how to handle an existing file and missing folders. A fixed path can overwrite or fail to save where expected if it is not checked.
Why does the exported sheet contain links to the original workbook?
The copied sheet may contain formulas or objects that refer to cells, sheets, or data outside the copied sheet. Copying the tab does not guarantee that every dependency is included. Review the exported file, include supporting sheets when needed, or convert suitable formulas to values.
Can I run an Excel VBA export macro in a browser?
No. Excel for the web does not run VBA. Open the workbook in desktop Excel for Windows or Mac to run the macro; the Mac version may require testing for dialog and path differences.
Related
- Excel Text to Columns: How to Split Data Into Fields
- Excel Text to Columns: Split Cell Text at Line Breaks
- Excel Text to Columns: Find It and Split Data Correctly
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- VBA Function As Boolean: How to Return True or FalseYes. In VBA, declare a function with As Boolean when it should return either True or False, then…
- VBA Access Arrays: Declare, Loop, and Resize ThemIn Microsoft Access VBA, an array is a variable that holds multiple values of the same declared type,…
- Excel VBA AutoFilter Exclude Criteria ExplainedTo exclude one exact value with Excel VBA AutoFilter, set the field’s criterion to a not-equal condition: Criteria1:="<>Closed".…
- VBA Copy Dictionary: Methods, Code, and Deep CopiesTo copy a VBA Scripting.Dictionary, create a second dictionary and add each key and item from the first;…
- VBA Add Border to a Range: Code, Styles, and FixesTo add borders to a range in Excel VBA, set the range’s Borders properties. For example, Range("B2:F10").Borders.LineStyle =…
- Excel VBA UserForm Coding: Build a Data-Entry FormExcel VBA UserForm coding means creating a custom dialog in Visual Basic for Applications and writing code that…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.