Excel · worked task guide

Excel VBA: Save a Copy Without Macros

SaveCopyAs does not have a FileFormat argument, so naming an .xlsm copy .xlsx does not convert it. Create a separate workbook or copy, then use SaveAs with xlOpenXMLWorkbook. Keep the macro-enabled original separate and verify what the export must preserve.

The word copy hides two different jobs. A backup should preserve the workbook and its VBA. A file for a recipient who should not receive VBA needs a different file format. Decide which job you are doing before choosing the method.

SaveCopyAs preserves; SaveAs converts

Choose the method by the required output
Method	What it does	Important consequence
Workbook.SaveCopyAs	Saves a copy without modifying the open workbook in memory.	Its argument is the filename, not a format-conversion option.
Workbook.SaveAs	Saves that workbook using a filename and optional FileFormat.	Call it on the export workbook when the original must remain your working .xlsm.
Worksheet.Copy, then SaveAs	Creates a separate workbook from the copied worksheets, then saves it as .xlsx.	It is a worksheet export, not a promise to clone all workbook-level features.

Microsoft documents SaveCopyAs with its filename-only signature and SaveAs with a FileFormat argument. The format enumeration identifies xlOpenXMLWorkbook as .xlsx and xlOpenXMLWorkbookMacroEnabled as .xlsm.

Do not pass FileFormat:=51 to SaveCopyAs. Do not merely rename the file extension either. The desired transition is an actual save in the target format, applied to the correct workbook object.

A guarded worksheet-export example

This example is for desktop Excel with VBA and a trusted workbook you control. It exports that workbook’s worksheets into a new workbook, asks for an .xlsx destination and rejects an already existing filename. It does not open downloaded files, change macro-security settings or remove modules through the VBA project object model.

Review the scope before running it on a disposable test workbook. Chart sheets, workbook-level behavior, external connections, names and macro-dependent formulas need separate preservation checks. This is not a complete binary copy, and this draft has not been runtime-tested in Excel.

Option Explicit

Public Sub ExportWorksheetsWithoutVBA()
    Dim source As Workbook, exported As Workbook
    Dim destination As Variant
    Dim previousEvents As Boolean
    Dim failureNumber As Long, failureText As String

    ' Put this procedure in the trusted source workbook, not PERSONAL.XLSB.
    Set source = ThisWorkbook
    destination = Application.GetSaveAsFilename( _
        InitialFileName:="report-export.xlsx", _
        FileFilter:="Excel Workbook (*.xlsx), *.xlsx")
    If VarType(destination) = vbBoolean Then Exit Sub
    If LCase$(Right$(CStr(destination), 5)) <> ".xlsx" Then
        MsgBox "Choose a filename ending in .xlsx.", vbExclamation
        Exit Sub
    End If

    previousEvents = Application.EnableEvents
    On Error GoTo Failed
    If Len(Dir$(CStr(destination))) > 0 Then
        Err.Raise vbObjectError + 1000, , "Choose a new filename; export will not overwrite a file."
    End If

    Application.EnableEvents = False
    ' Copies worksheets only; it is NOT a complete workbook clone.
    source.Worksheets.Copy
    Set exported = ActiveWorkbook
    If exported Is source Then
        Set exported = Nothing
        Err.Raise vbObjectError + 1001, , "No separate export workbook was created."
    End If

    ' Format 51 is .xlsx. Keep Excel's compatibility prompts visible.
    exported.SaveAs Filename:=CStr(destination), _
        FileFormat:=xlOpenXMLWorkbook, AddToMru:=False
    exported.Close SaveChanges:=False
    Set exported = Nothing
    Application.EnableEvents = previousEvents
    MsgBox "Export saved. Reopen and verify it before sharing.", vbInformation
    Exit Sub

Failed:
    failureNumber = Err.Number
    failureText = Err.Description
    ' Suppress only cleanup failures, not the failed export itself.
    On Error Resume Next
    If Not exported Is Nothing Then exported.Close SaveChanges:=False
    Application.EnableEvents = previousEvents
    On Error GoTo 0
    MsgBox "Export failed (" & failureNumber & "): " & failureText, vbExclamation
End Sub

Place the procedure in a standard module in the source workbook. Here, ThisWorkbook means the workbook containing the procedure—not necessarily the workbook most recently clicked. Putting it in PERSONAL.XLSB would therefore select the wrong source.

Immediately after copying, the new workbook is captured as exported. Every save and close then uses that variable, rather than repeatedly guessing from whichever workbook is active. The error handler restores the previous event setting and reports the failure; it does not claim that a failed export succeeded.

The procedure leaves the current alert setting unchanged and does not intentionally suppress compatibility dialogs. Run it from a normal interactive Excel session with alerts enabled; an automation host that disabled alerts needs its own prompt and overwrite policy. The filename existence check is a guard for an interactive workflow, not an atomic lock against another process creating the same file at the same instant.

Microsoft’s Worksheet.Copy reference documents the new-workbook behavior and notes that worksheet code can be carried into the copy. Saving as the macro-free format is therefore the decisive conversion step, not copying alone.

When you need a full workbook copy instead

For a one-off conversion where workbook-level features matter, first make a backup and work on a separately named copy. Open only a file you trust, save that copy as an Excel Workbook (.xlsx), and read the compatibility warning before accepting the loss of VBA. Keep the .xlsm original unchanged.

Automating that full-copy workflow needs more than two save commands: unique temporary paths, a deliberate file-opening security policy, links and refresh behavior, prompts, cleanup after failure, and protection against overwriting an existing destination. This page does not silently substitute a worksheet export for that broader promise.

Verify the export with a small, observable workbook

Use a test workbook with two clearly named sheets, a plain formula such as =2+3, a chart, a workbook name, and a harmless macro you can identify. Save the original, run the export to a new filename and reopen the .xlsx. Check that the ordinary formula still gives 5 and that all required visible content is present.

Then check the features that can fail quietly: a button assigned to a macro, a formula calling a VBA-defined function, links back to the original, hidden sheets, names and connections. Decide whether recipients need functioning formulas or a values-only report. Removing VBA does not automatically turn VBA-dependent calculations into independent spreadsheet formulas.

Finally, return to the original .xlsm and confirm that its filename, VBA and expected behavior remain intact. Try cancelling the destination dialog and choosing an existing filename as separate tests. Neither should overwrite your source or produce a success message.

Common questions

Does .xlsx mean that a workbook is safe to share?

No. A macro-free format is not a privacy review. Inspect hidden content, comments, metadata, external links and the actual information in the sheets before sharing a file.

Can I keep formulas?

Ordinary formulas can remain formulas. Anything depending on removed VBA or another workbook needs a separate decision and a reopen-and-recalculate test.

Why not use ThisWorkbook.SaveAs directly?

That targets the workbook containing the code. In this task the original must remain the working macro-enabled file, so the example saves a separately captured export workbook instead.

Primary-source/API review: 25 September 2026. Original example, not a claim of desktop Excel execution. Full workbook preservation, security scanning and values-only conversion are outside this worksheet-export procedure.