VBA Save Workbook in Excel — Save vs SaveAs, the FileFormat Trap, and Why Your Macros Disappear

TL;DR — .Save overwrites the file you already have. .SaveAs writes a new file or type, and you must give it the right FileFormat number or you strip the macros out of the workbook. A macro-enabled .xlsm needs xlOpenXMLWorkbookMacroEnabled (52), not the .xlsx format (51):

Sub SaveAsMacroWorkbook()
    Application.DisplayAlerts = False               ' pre-approve the overwrite prompt
    ThisWorkbook.SaveAs _
        Filename:="C:\Reports\Q1.xlsm", _
        FileFormat:=xlOpenXMLWorkbookMacroEnabled   ' 52 = .xlsm, keeps the VBA
    Application.DisplayAlerts = True                ' restore immediately
End Sub

Saving looks like the simplest thing a macro can do, and it hides the sharpest data-loss trap in the whole workbook object model: the wrong FileFormat deletes your code without a word of warning. This guide is built on the one distinction that prevents it — Save keeps the file you have, SaveAs makes a new one, and the new one is only correct if the format matches the extension.

What you'll learn

The mental model — Save commits in place; SaveAs writes a new file and needs a FileFormat
The trap that eats your macros — saving a .xlsm with the .xlsx format number strips all the VBA
Why .Save on a never-saved workbook pops the Save As dialog and hangs an unattended macro
How DisplayAlerts turns the overwrite prompt into a pre-approval, not a mute button
What wb.Saved = True really does (mark clean) versus what it does not do (write the file)
When to reach for SaveCopyAs and ExportAsFixedFormat instead

The mental model: Save commits, SaveAs creates

Two verbs, two jobs:

.Save overwrites the workbook's existing file, in place, silently. No dialog, no new name. It is Ctrl+S. It only works if the workbook already has a file on disk.
.SaveAs writes the workbook to a new path or a new file type. It is File > Save As. Because it is creating a file, it needs to know the format — and that is where the trap lives.

ThisWorkbook.Save                                   ' overwrite the current file
ThisWorkbook.SaveAs "C:\Reports\Copy.xlsx", xlOpenXMLWorkbook   ' write a new .xlsx

Everything below is a consequence of that one split.

The trap that eats your macros: FileFormat must match the extension

This is the failure that makes people think Excel "lost" their code. You save a workbook that contains macros, but with the plain-workbook format number:

ThisWorkbook.SaveAs "C:\Reports\Q1.xlsm", xlOpenXMLWorkbook   ' 51 = .xlsx = NO macros

xlOpenXMLWorkbook (51) is the .xlsx format, and .xlsx cannot hold VBA. Excel writes the file and silently discards every module and macro — even though you named the file .xlsm. Now the extension says "macro-enabled" and the contents say "no macros," so Excel warns or refuses to open it cleanly, and your code is gone.

The rule is simple: the FileFormat number must agree with the extension. The four you actually use:

' FileFormat constants (and their numeric values):
'   xlOpenXMLWorkbook               = 51   .xlsx   (no macros)
'   xlOpenXMLWorkbookMacroEnabled   = 52   .xlsm   (keeps macros)
'   xlExcel8                        = 56   .xls    (old 97-2003)
'   xlCSV                           =  6   .csv    (one sheet, values only)

If the workbook has code, it must be .xlsm with 52. When in doubt, use the named constant, not the raw number — xlOpenXMLWorkbookMacroEnabled cannot be mistyped the way 52 can.

Save on a never-saved workbook pops a dialog

.Save overwrites the existing file — but a workbook created with Workbooks.Add has no file yet. Calling .Save on it cannot know where to write, so Excel falls back to the Save As dialog — which hangs any macro running unattended. The first time you write a new workbook, always use .SaveAs with an explicit path and format:

Dim wb As Workbook
Set wb = Workbooks.Add
' wb.Save                                            ' WRONG on a new book - pops Save As dialog
wb.SaveAs "C:\Reports\New.xlsx", xlOpenXMLWorkbook   ' right - give it a path the first time
wb.Save                                              ' now .Save works - the file exists

The overwrite prompt is a pre-approval, not a mute button

SaveAs to a path that already exists raises the modal "A file already exists — replace it?" prompt. To run unattended you suppress it:

Application.DisplayAlerts = False
wb.SaveAs "C:\Reports\Q1.xlsm", xlOpenXMLWorkbookMacroEnabled
Application.DisplayAlerts = True

Read that honestly: setting DisplayAlerts = False does not silence the question, it answers it with the default — yes, replace. You have pre-approved overwriting whatever was already at that path. That is exactly the point made in VBA DisplayAlerts: you are not hiding a warning, you are agreeing to the destructive default in advance. Restore DisplayAlerts = True on the very next line so the next prompt is not silently auto-confirmed too.

Saved = True marks clean; it does not save

wb.Saved is the "dirty" flag — False means "there are unsaved changes." You will see this trick to suppress the close prompt:

wb.Saved = True     ' tell Excel "no unsaved changes" - does NOT write the file
wb.Close            ' closes without the "save changes?" prompt

This is useful, but know exactly what it does: it lies to Excel about the dirty state so the close prompt does not appear. It writes nothing to disk. If you actually wanted the changes kept, wb.Saved = True throws them away. Marking-clean and saving are different operations — do not confuse them.

SaveCopyAs and PDF: the two SaveAs look-alikes

Two jobs look like SaveAs but are not:

SaveCopyAs writes a backup copy to disk without changing the workbook's own path or dirty state. It is the right tool for "snapshot the file before I mutate it," because the live workbook keeps its original name and stays open:
```
ThisWorkbook.SaveCopyAs "C:\Backups\before-run.xlsm"   ' copy out; original untouched
```
PDF is not a SaveAs format. There is no "SaveAs PDF" — exporting to PDF is a different method, ExportAsFixedFormat:
```
ThisWorkbook.ExportAsFixedFormat Type:=xlTypePDF, Filename:="C:\Reports\Q1.pdf"
```

The honest verdict: which save to use

.Save — commit changes to a workbook that already has a file. The everyday case.
.SaveAs path, FileFormat — write a new file or convert type. Always pass an explicit FileFormat, and make it match the extension: 52 for .xlsm, 51 for .xlsx, 6 for .csv.
.SaveCopyAs — a backup snapshot that leaves the live workbook alone.
ExportAsFixedFormat — PDF/XPS output, not a save format.

The one non-negotiable: if the workbook holds code, it is .xlsm with xlOpenXMLWorkbookMacroEnabled. Every "where did my macros go?" ticket is that number being wrong. And any SaveAs in an automated run needs DisplayAlerts = False around it — with an immediate restore — so the overwrite prompt does not hang the job or, worse, get silently answered for the next file too.

How ExcelMaster helps

The save step is where a working macro quietly destroys work: the wrong FileFormat deletes the VBA, .Save on a new book hangs on a dialog, an un-restored DisplayAlerts auto-confirms the next prompt, and Saved = True throws away changes you meant to keep. Each is a small detail; each loses data.

ExcelMaster gets the save right by construction. Ask it to "save each region's sheet as its own macro-enabled workbook" and it writes .SaveAs with xlOpenXMLWorkbookMacroEnabled, wraps the overwrite in a DisplayAlerts off/on pair, uses SaveCopyAs when you want a backup rather than a rename, and .Save only on workbooks that already have a path. You describe the file you want; it picks the format that keeps your code and your data.

Frequently asked questions

What is the difference between Save and SaveAs in VBA?

.Save overwrites the workbook's existing file in place, silently, with no dialog — the equivalent of Ctrl+S. .SaveAs writes the workbook to a new path or a new file type and requires a FileFormat argument. Use .Save to commit changes to a file that already exists, and .SaveAs to create a new file or convert the format.

Why did VBA delete my macros when I saved the workbook?

You saved a macro workbook with the .xlsx format number, xlOpenXMLWorkbook (51). The .xlsx format cannot store VBA, so Excel silently drops every module even though the file is named .xlsm. Save macro-enabled workbooks with xlOpenXMLWorkbookMacroEnabled (52) so the code is kept.

How do I save a workbook in VBA without the overwrite prompt?

Set Application.DisplayAlerts = False before the SaveAs, then restore it to True immediately after. This answers the "file already exists, replace?" prompt with its default (yes, replace), so it does not appear. Remember that you have pre-approved overwriting the existing file, so be sure that is what you want.

What does wb.Saved = True do in VBA?

It sets the workbook's dirty flag to "no unsaved changes" so Excel does not show the "save changes?" prompt on close. It does not write anything to disk. If there really were unsaved changes, setting Saved = True discards them. To keep the changes, call .Save or close with SaveChanges:=True.

How do I save a workbook as PDF in VBA?

PDF is not a SaveAs format. Use ExportAsFixedFormat: ThisWorkbook.ExportAsFixedFormat Type:=xlTypePDF, Filename:="C:\Reports\Q1.pdf". To write a plain backup copy of the workbook itself without changing its name, use SaveCopyAs instead.

Tested in

Tested in: Excel 365 (Windows 11), VBA 7.1 — last verified 2026-08-20.