Wauvel

Excel like a finance pro.

← All VBA snippets

Save a dated copy without closing what you're working on

The poor man's version control, and the honest answer to "which version did we send the bank?"

1What it does

`SaveCopyAs` writes a copy to disk and leaves the file you're in open and untouched — unlike `SaveAs`, which renames the workbook you're working on and quietly means every later save goes to the new name. Put the date in the filename in `yyyy-mm-dd` form so the archive sorts chronologically, and you have a record of what the model said on the day you sent it. That's the difference between answering "which version went to the bank?" and guessing.

2The code

Sub ArchiveDatedCopy()
    Dim archiveDir As String, baseName As String, target As String

    archiveDir = ThisWorkbook.Path & Application.PathSeparator & "Archive"

    ' Create the folder the first time, and only then.
    If Dir(archiveDir, vbDirectory) = "" Then MkDir archiveDir

    ' Strip the extension off the current name.
    baseName = ThisWorkbook.Name
    If InStrRev(baseName, ".") > 0 Then
        baseName = Left$(baseName, InStrRev(baseName, ".") - 1)
    End If

    ' yyyy-mm-dd so the archive folder sorts chronologically.
    target = archiveDir & Application.PathSeparator & _
             baseName & "_" & Format(Date, "yyyy-mm-dd") & ".xlsx"

    ' SaveCopyAs, NOT SaveAs — this workbook stays open under its own name.
    ThisWorkbook.SaveCopyAs target

    MsgBox "Archived to:" & vbNewLine & target, vbInformation
End Sub

Paste into the Visual Basic Editor (Alt + F11 → Insert → Module), then run and save as macro-enabled (.xlsm).

New to macros? Set up in 5 minutes▾
  1. 1

    Don't see the Developer tab in the ribbon?

    You don't strictly need it — Alt + F11 opens the editor directly — but it makes running macros easier.
    • Windows: File → Options → Customize Ribbon → tick Developer in the right-hand list → OK.
    • Mac: Excel → Preferences → Ribbon & Toolbar → tick Developer → Save.
  2. 2

    Paste in the code

    Press Alt + F11 to open the Visual Basic editor, then Insert → Module and paste the snippet's code into the blank window. Close it with Alt + Q.
  3. 3

    Run it

    Press Alt + F8, pick the macro's name, and click Run — that's it. (Pasted a custom function instead? Just type it into a cell like any built-in: =GrossMargin(B2, B3).)
  4. 4

    Keep the macro — save as .xlsm

    File → Save As → Excel Macro-Enabled Workbook (.xlsm). A plain .xlsx silently drops the code when you save.
  5. 5

    Macros blocked?

    Click Enable Content on the yellow bar. If you downloaded the file, you may first need to right-click it → Properties → tick Unblock → OK, then reopen.

Heads up: macros can't be undone with Ctrl + Z — save a copy before running one that changes your workbook.

3When you use it

  • Snapshot the model before a board meeting, or before someone else edits it.
  • Keep a dated trail of what was sent to a lender.
  • Archive month-end automatically at the end of a close macro.

4See it in action

A simulation — press Run to perform what the macro does to a sample workbook.

Open in Excel right now
FileModel.xlsx
Next Ctrl+S writes toModel.xlsx
Archive folder
(empty)

Both write the same file. Only one of them leaves you where you were.

5Pitfalls

`SaveAs` used instead. The open workbook is renamed, so every later save goes to the archive copy and the original stops updating.

Fix: `SaveCopyAs` writes and forgets. It's the whole point of this macro.

Format(Date, "dd/mm/yyyy") produces slashes, which aren't legal in a filename.

Fix: Use yyyy-mm-dd — legal everywhere, and it sorts correctly in the folder.

`MkDir` errors if the folder already exists.

Fix: Guard with `If Dir(path, vbDirectory) = ""` as above.

A macro-enabled workbook saved with a .xlsx extension loses its macros, or refuses.

Fix: Match the extension to the file: .xlsm for a macro workbook.

6No-code alternatives

  • OneDrive / SharePoint version history — Genuinely better if the file lives there — automatic, and it keeps far more versions than anyone archives by hand.
  • SaveAs with a new name — Right when you WANT to continue in the new file. Wrong for archiving.

Rather not write macros? Wauvel's free tools generate branded, formula-driven Excel for you — no VBA required.

Learn the moves here — or let Wauvel run them on your numbers.

Meet your AI CFO →

One CFO-grade Excel tip a week

A short, practical email for finance operators — functions, shortcuts, and the moves that save an afternoon. Free, unsubscribe anytime.