Overview
SaveCopyAs alone cannot produce a macro-free .xlsx file from an .xlsm workbook. The method saves an as-is copy in the same file format as the original, so the copy still contains your VBA project. To get a genuine .xlsx, you must run SaveAs with xlOpenXMLWorkbook on a temporary copy or a newly created workbook, never on the workbook hosting the running code.
The reason is in the method’s definition. Microsoft’s documentation for Workbook.SaveCopyAs shows that it “saves a copy of the workbook to a file but doesn’t modify the open workbook in memory,” and its only parameter is FileName. There is no FileFormat argument, so there is no conversion step. As an accepted answer on Experts Exchange puts it: “You cannot change the type with SaveCopyAs - you have to SaveAs if you want to change it.” The same answer adds that “simply altering the extension has no effect at all.” Renaming a .xlsm copy to .xlsx changes the label, not the underlying file format, and the macros stay inside.
That leaves you two supported workflows. First, save a temporary as-is copy with SaveCopyAs, open that copy, convert it with SaveAs, and close it, which keeps your original .xlsm open and running. Second, copy selected sheets into a new workbook and save that new workbook as .xlsx. The rest of this article walks through both, then covers format values, prompts, cleanup, and the most common failure points.
Use SaveCopyAs, then reopen and convert the copy
The core workflow is a four-step sequence: SaveCopyAs to a temporary .xlsm file, open that copy, SaveAs the copy with FileFormat:=xlOpenXMLWorkbook, and close it. Your original macro-enabled workbook stays open throughout, so any code that follows keeps running. This is the pattern recommended on Experts Exchange: “You’d need to do savecopyas, open the copy, saveas new format, then close it. You can then email the macro-free version, and delete both copies if required.”
Understanding which workbook each method touches is what makes the sequence safe. According to Microsoft’s documentation, SaveCopyAs writes a copy to disk without modifying the open workbook in memory, so after step 1 your source workbook is exactly where it was. Workbook.SaveAs, by contrast, “saves changes to the workbook in a different file,” meaning it acts on the workbook object you call it on. That is why you call SaveAs on the reopened copy, not on ThisWorkbook. A Stack Overflow answer makes the same distinction: ThisWorkbook addresses the workbook that contains the code, and once you save ThisWorkbook in .xlsx format the code cannot continue to run. Keeping the conversion on a separate workbook object avoids that trap entirely.
Here is a compact worked example based on the pattern documented at vmlogger, which uses originalWB.SaveCopyAs (tempXLSMPath) followed by .SaveAs fileName:=tempXLSXPath, FileFormat:=xlOpenXMLWorkbook and .Close savechanges:=False. Adjust the paths and file names to match your environment before running it:
Sub SaveMacroFreeCopy()
Dim originalWB As Workbook
Dim tempWB As Workbook
Dim tempXLSMPath As String
Dim outputXLSXPath As String
Set originalWB = ThisWorkbook
tempXLSMPath = "C:\Reports\Temp\ReportCopy.xlsm"
outputXLSXPath = "C:\Reports\Output\Report.xlsx"
' Step 1: save an as-is copy; the original stays open and unchanged
originalWB.SaveCopyAs tempXLSMPath
' Step 2: open the copy so SaveAs acts on it, not on the original
Set tempWB = Workbooks.Open(tempXLSMPath)
' Step 3: convert the copy to a macro-free .xlsx
Application.DisplayAlerts = False
tempWB.SaveAs Filename:=outputXLSXPath, FileFormat:=xlOpenXMLWorkbook
Application.DisplayAlerts = True
' Step 4: close the converted workbook and remove the temporary copy
tempWB.Close SaveChanges:=False
Kill tempXLSMPath
End Sub
Three details in this example are easy to miss when adapting it. First, the temporary copy must be saved with an .xlsm extension because SaveCopyAs preserves the source format; giving it an .xlsx name would only mislabel a macro-enabled file. Second, tempWB is an explicit workbook variable set from Workbooks.Open, so the SaveAs call is unambiguous even if the user activates another window while the macro runs. Third, SaveChanges:=False on the Close call is correct because the SaveAs already wrote the .xlsx to disk; there is nothing left to save.
After the procedure ends you have your original .xlsm still open and unmodified, a finished .xlsx at the output path, and no leftover temporary file. Your automation can continue in the source workbook, attach the output to an email, or move it to a shared location.
Save as .xlsx with xlOpenXMLWorkbook
The format that produces a standard macro-free workbook is the named constant xlOpenXMLWorkbook, passed through the FileFormat parameter that Workbook.SaveAs documents as accepting values from the XlFileFormat enumeration. The vmlogger walkthrough uses exactly this form: SaveAs Filename:="C:\...\abc.xlsx", FileFormat:=xlOpenXMLWorkbook.
You will also see the numeric value 51 in community code, as in the MrExcel thread where the working line is ActiveWorkbook.SaveAs FileName:=Path & "\" & sNewWorkbookName & " - " & [G5], FileFormat:=51. The number works, but the named constant is the better habit. Unexplained numbers invite the kind of mistake covered in the troubleshooting section below, where a poster used FileFormat:=15 expecting .xlsx and got a Lotus 1-2-3 format instead.
When Excel saves a workbook that contains VBA into a macro-free format, it raises a confirmation prompt about losing the VBA project. Both the vmlogger and MrExcel sources handle this the same way: set Application.DisplayAlerts = False immediately before the SaveAs, then set it back to True immediately on the next line. The restoration matters. DisplayAlerts is an application-level setting, so leaving it off suppresses every subsequent Excel warning in the session, including ones that protect the user from overwriting or discarding work. Keep the suppressed window as narrow as the single SaveAs statement.
Preflight, error cleanup, and application-state restoration
A conversion macro that works once on your machine is not the same as one that is safe to reuse. Before the SaveCopyAs call, it is worth checking a few conditions that the happy-path example above simply assumes. The supplied sources do not document a tested universal handler for these branches, so treat the following as decision points you implement to fit your workflow rather than guaranteed behavior:
- Destination collision. If the output .xlsx or the temporary .xlsm already exists at the target path, decide explicitly whether to overwrite, rename with a timestamp, or stop with a message. Silent overwrites are the riskiest default for a distributed report.
- User cancellation. If you prompt for a destination (for example with a save dialog), handle the cancel case before calling SaveAs, so a cancelled dialog does not feed an invalid filename into the conversion.
- Unsaved source workbook. SaveCopyAs copies the workbook as it currently exists in memory per Microsoft’s definition, so decide whether unsaved changes should be included in the copy or whether the source should be saved first.
On the cleanup side, the principle is narrow but firm: restore every application setting the procedure actually changes, and remove every temporary resource it creates. In the worked example, that means Application.DisplayAlerts must return to True even if the SaveAs fails, which argues for an error handler that performs the restoration before exiting. The same handler should attempt to close the temporary workbook without saving and delete the temporary .xlsm, following the Kill tempXLSMPath pattern shown at vmlogger. If your version of the macro also changes ScreenUpdating or EnableEvents, restore those too; if it does not change them, there is nothing to restore. A cleanup routine that mirrors exactly what the procedure changed is easier to verify than a generic reset of every setting.
Close, use, and remove temporary files
The order of operations after conversion matters: close first, use second, delete last. Close the converted workbook with SaveChanges:=False as soon as the SaveAs completes, because the .xlsx file already exists on disk and holding the workbook open only risks locking the file when a downstream process tries to read or attach it.
Once the file is closed, you can use it however your workflow requires. The vmlogger example attaches the output with .Attachments.Add tempXLSXPath in an email routine, and the Experts Exchange answer describes the same shape of workflow: email the macro-free version, then delete both copies if required. The specifics of email automation are outside this article’s scope, but the sequencing rule holds for any consumer of the file.
Delete temporary files only after every use of them has finished. The vmlogger pattern ends with Kill tempXLSMPath and Kill tempXLSXPath, run after the attachment step. If you kill the .xlsx before the email or copy operation completes, the downstream step fails with a missing file.
Alternative: export selected sheets to a new workbook
If the recipient only needs some of your sheets, you can skip the whole-workbook copy and instead copy selected sheets into a new workbook, then save that new workbook as .xlsx. Because the SaveAs runs against a workbook that never contained your modules, the running macro in the source .xlsm is not interrupted.
The pattern appears in a Stack Overflow thread where the asker had exactly the problem this article opens with: saving the current workbook as non-macro-enabled meant “I obviously cannot run the rest of the macros that I need.” The accepted approach copies sheets out instead:
Dim newWB As Workbook
Set newWB = Workbooks.Add
ThisWorkbook.Sheets(Array("SheetA", "SheetB")).Copy Before:=newWB.Sheets(1)
newWB.SaveAs Filename:="C:\Reports\Output\Extract.xlsx", _
FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
Note the explicit references. The thread also shows a looser variant that copies sheets and then calls ActiveWorkbook.SaveAs, relying on the fact that a sheet copy with no destination creates and activates a new workbook. That version can work, but ActiveWorkbook is fragile: if anything changes which window is active between the copy and the save, the SaveAs targets the wrong workbook. Assigning the new workbook to a variable with Workbooks.Add and copying Before:=newWB.Sheets(1), as the thread’s second snippet does, removes the ambiguity. You will still need to handle the blank default sheet that Workbooks.Add creates, typically by deleting it after the copy.
One caveat from the corpus deserves emphasis before you rely on this method. A Tek-Tips discussion of the whole-sheet-copy approach reports that while VBA module folders and ThisWorkbook code were not copied into the new workbook, “the sheet macro’s are still present,” meaning code attached to the copied worksheets came along. Saving the new workbook as .xlsx with alerts handled strips the VBA project at that point, but if you save the intermediate workbook in a macro-capable format, worksheet-level code may survive. The safest habit is to treat the .xlsx SaveAs as the step that guarantees a macro-free result, and to verify the output by opening it and checking the VBA editor.
Whole-sheet copy versus a values-and-formatting snapshot
The choice between copying whole sheets and copying only their content depends on what the recipient should receive. A whole-sheet copy brings the sheet as an object, while a content copy pastes ranges into fresh sheets, and the two behave differently in ways the evidence only partially documents.
For content-level copying, Excel Campus states the baseline plainly: “The Range.Copy method does a regular copy and paste that includes formatting and formulas. If you just want to paste values, there is an example below.” That gives you two content options. A regular Range.Copy into the new workbook carries formulas and formatting, which means formulas referencing sheets you did not export will break or turn into external links to your source file. A values-oriented paste avoids that by writing results instead of formulas, which is often what you want in a distributed report. The supplied evidence confirms the values alternative exists but does not fully specify a tested values-plus-formatting implementation, so if you need both values and formatting, build it with a values paste followed by a formats paste and verify the result in your own workbook.
For whole-sheet copying, the Tek-Tips thread contributes two specific observations: the array-copy approach “works with both xlvisible and xlhidden sheets but does not copy xlveryHidden” sheets, and worksheet-level code can travel with copied sheets even when standard modules do not. If your workbook uses xlVeryHidden sheets as lookup or staging areas, a whole-sheet copy will silently exclude them, which may be exactly what you want for distribution or may break formulas that depend on them.
Beyond these documented points, the corpus does not provide a tested preservation matrix. Treat external links, defined names, charts, form and ActiveX controls, hidden-state fidelity, and workbook-level event behavior as items to verify in your own output rather than outcomes you can assume. Open the exported .xlsx, check the features your recipients depend on, and adjust the copy strategy if something important did not survive.
Remove only named macro buttons
Saving as .xlsx removes the VBA project, but it does not necessarily remove the visible buttons that used to trigger your macros, and a button that does nothing confuses recipients. As a reply in an ExcelForum thread explains, “when you save as xlsx the code will be not saved, you don’d need delete id, you need only delete button,” with the targeted line ActiveSheet.Shapes.Range(Array("Button 1")).Delete.
The principle to take from that snippet is deletion by name. Reference the specific shapes you want gone, using the names Excel assigned or the names you set, and leave everything else in place. Run the deletion on the temporary copy or the new export workbook, before the final SaveAs, so your source workbook keeps its buttons.
The alternative, looping through every shape, is demonstrably risky. In the MrExcel thread, a poster used For Each Shp In ActiveSheet.Shapes: Shp.Delete: Next Shp and reported the consequence directly: “it also got rid of the option buttons, which should be allowed to remain.” The Shapes collection includes controls, pictures, and drawing objects alike, so a blanket delete removes things you meant to keep. If you cannot enumerate the buttons by name, filter the loop by whatever property reliably distinguishes your macro buttons in your workbook, and test the result before distributing it.
Troubleshoot paths, filenames, extensions, and FileFormat values
When the macro-free save fails or lands somewhere unexpected, the cause is usually one of a small set of mismatches between the name, the path, and the format. Work through them in order.
The extension does not match the format. The filename extension and the FileFormat argument are independent, and Excel does not reconcile them for you in the way readers often expect. As the Experts Exchange answer notes for SaveCopyAs, altering the extension “has no effect at all” on the content. Always pass an extension that matches the format constant: .xlsx with xlOpenXMLWorkbook.
A numeric FileFormat that is not what you think. A Microsoft Tech Community thread diagnoses a failed save where the poster wrote wb.SaveAs Filename:=strFile, FileFormat:=15. The responder points out that “file format 15; this stands for .wk3, a Lotus 1-2-3 file format!” and that the intended value was xlOpenXMLWorkbook. This is the strongest argument for named constants: 51 happens to be .xlsx, 15 is not, and nothing in the code tells you which is which.
The wrong variable in the Filename argument. The same thread shows a second bug in one line: the code passed strFile when the correct, fully built name was in myFile. When a SaveAs fails or produces an oddly named file, print the exact filename variable to the Immediate window before the call and confirm it is the one you constructed.
No path means the current folder. Per Microsoft’s SaveAs documentation, the FileName parameter can include a full path, and “if you don’t, Microsoft Excel saves the file in the current folder.” A file that seems to vanish has usually been saved to whatever folder happens to be current, which can differ between sessions and machines. Always build a full path.
Generated names that violate naming constraints. When filenames or sheet names are built from cell values, they can inherit characters or lengths Excel rejects. The Tech Community thread flags the constraints for the sheet name involved in that case: a maximum length of 31 characters, and none of the characters \, /, *, ?, :, [, or ]. If your macro concatenates cell contents into names, as the MrExcel example does with sNewWorkbookName & " - " & [G5], sanitize the result before using it.
If a save still fails after these checks, isolate the SaveAs onto a hardcoded literal path and format constant. If the literal version works, the problem is in how your variables are built, not in the method call.
Does a running procedure actually stop immediately after its workbook is directly saved as macro-free .xlsx, and does behavior vary by Excel version?
The supplied sources consistently warn that it does stop, but none of them provides authoritative or version-by-version verification, so you should treat the behavior as a risk to design around rather than a rule to rely on. The vmlogger article states that “as soon as Save As command is run, then in the current workbook there is no macro any more and code will stop running there and further statements will not be executed any more.” A Stack Overflow answer says the same thing: once you save ThisWorkbook in .xlsx format, the code cannot continue to run. A second Stack Overflow thread describes the practical consequence, where the poster could not run the rest of the needed macros after the direct save.
These are community reports, not Microsoft documentation, and the corpus contains no reproducible cross-version test. The practical recommendation follows either way: never make your workflow’s correctness depend on statements that execute after a direct SaveAs of the code-hosting workbook. Convert a temporary copy or a new workbook instead, as shown above, or place the automation in a separate host such as an add-in, an option the Stack Overflow answer also suggests. Then the question of whether execution survives becomes irrelevant to your workflow.