Timestamped Backup: One-Click Snapshots Before Every Destructive Change

TL;DR: Before running a destructive macro, before emailing the file to the client, before starting the next review cycle — you want a snapshot. This macro saves a copy with the current date-time stamped into the filename (e.g., Henderson-1120S_2026-12-15_1445.xlsm). One click. No typing. Reports the filename so you know exactly what was saved.

#Prerequisites & Setup

What you’ll need:

• Excel 2016+ (desktop)
• A workbook that has been saved at least once (has a file path)
• The macro stored in your Personal Macro Workbook (PERSONAL.XLSB) so it’s available for every file you open

What the macro does:

• Uses ThisWorkbook.SaveCopyAs to save a copy without changing the active workbook’s file path
• Builds the filename from the current workbook name by stripping the extension and appending a timestamp like _2026-12-15_1445
• Reports the new filename in a MsgBox so you can verify

Limitations:

• If the workbook has never been saved (ThisWorkbook.Path is empty), the macro prompts you to save it first
• Uses ThisWorkbook — store it in your Personal Macro Workbook if you want to back up any open file. Change ThisWorkbook to ActiveWorkbook in the code
• The file extension is always .xlsm (macro-enabled). If your workbook is .xlsx or .xlsb, the backup will have a different extension — Excel will warn on open. To fix this, change the extension in the code or save as .xlsb for Personal Macro Workbook compatibility
• If a file with the same timestamped name already exists (e.g., you run the macro twice in the same minute), SaveCopyAs will overwrite it without warning. The timestamp goes down to the minute — running twice in the same minute is rare in practice, but be aware

#The Macro

Option Explicit

Sub TimestampedBackup()
    ' ── Timestamped Backup ────────────────────────────
    ' Saves a copy of the current workbook in the same
    ' folder with a date-time stamp in the filename.
    '
    ' Example: Henderson-1120S_2026-12-15_1445.xlsm
    '
    ' Zero input. Non-destructive — saves a COPY, does
    ' not change the original file's path or name.
    ' ────────────────────────────────────────────────────

    ' ── Configuration ──────────────────────────────────
    Const TIME_FORMAT As String = "yyyy-mm-dd_hhmm"

    ' ── Variables ──────────────────────────────────────
    Dim baseName As String
    Dim timestamp As String
    Dim backupPath As String
    Dim dotPos As Long

    ' ── Error handling ─────────────────────────────────
    On Error GoTo CleanUp

    ' ── Check for unsaved workbook ─────────────────────
    If Len(ThisWorkbook.Path) = 0 Then
        MsgBox "Save the workbook first. A backup can't " & _
               "be created for an unsaved file.", _
               vbExclamation, "Timestamped Backup"
        Exit Sub
    End If

    ' ── Build backup filename ──────────────────────────
    timestamp = Format(Now, TIME_FORMAT)

    dotPos = InStrRev(ThisWorkbook.Name, ".")
    If dotPos > 0 Then
        baseName = Left(ThisWorkbook.Name, dotPos - 1)
    Else
        baseName = ThisWorkbook.Name
    End If

    backupPath = ThisWorkbook.Path & "\" & _
                 baseName & "_" & timestamp & ".xlsm"

    ' ── Save the copy ──────────────────────────────────
    ThisWorkbook.SaveCopyAs backupPath

    ' ── Report results ─────────────────────────────────
    MsgBox "Backup saved as:" & vbCrLf & vbCrLf & _
           Dir(backupPath), vbInformation, "Timestamped Backup"

CleanUp:
    If Err.Number <> 0 Then
        MsgBox "Error " & Err.Number & ": " & Err.Description & _
               vbCrLf & vbCrLf & _
               "Check that the target folder is writable and " & _
               "the filename is valid.", _
               vbCritical, "Macro Error"
    End If
End Sub

#How It Works

#SaveCopyAs vs SaveAs — the critical difference

Excel’s VBA object model gives you two ways to save a file, and using the wrong one will cost you. ThisWorkbook.SaveCopyAs saves a duplicate under a new name but leaves the active workbook’s path and filename untouched. You stay in your original file. ThisWorkbook.SaveAs renames the active workbook — suddenly you’re editing Henderson_2026-12-15_1445.xlsm instead of Henderson.xlsm. Every subsequent save goes to the backup file, not your real workpaper.

ThisWorkbook.SaveCopyAs backupPath    ' Correct — copy only
' ThisWorkbook.SaveAs backupPath      ' Wrong — renames active workbook

This is the single most important design decision in the macro. Tax preparers who accidentally use SaveAs instead of SaveCopyAs find themselves saving journal entries into a timestamped file 20 minutes later, wondering why the “real” workbook doesn’t have their latest changes.

#Stripping the extension to build the backup name

The filename of the backup is built from the current workbook’s name. If your file is Henderson-1120S.xlsm, the macro extracts Henderson-1120S, appends the timestamp, and adds .xlsm back:

dotPos = InStrRev(ThisWorkbook.Name, ".")
baseName = Left(ThisWorkbook.Name, dotPos - 1)
backupPath = ThisWorkbook.Path & "\" & _
             baseName & "_" & timestamp & ".xlsm"

InStrRev searches from the right to handle filenames with multiple dots — Q1.Review.Draft.xlsm correctly becomes Q1.Review.Draft_2026-12-15_1445.xlsm, not Q1_2026-12-15_1445.xlsm.

The timestamp format yyyy-mm-dd_hhmm sorts chronologically in File Explorer. Alphabetical order IS date order. A space or slash in the filename would break this, which is why the format uses dashes and underscores instead.

#The unsaved-workbook guard

If the workbook has never been saved, ThisWorkbook.Path is an empty string. SaveCopyAs requires a full path — you can’t save to \Henderson.xlsm. The guard catches this upfront:

If Len(ThisWorkbook.Path) = 0 Then
    MsgBox "Save the workbook first..."
    Exit Sub
End If

This is most common when you create a new workbook from a template (File → New) and immediately try to run the macro. Save once, then the macro works forever.

#Why no ScreenUpdating or Calculation toggle

This macro writes exactly one file to disk — it doesn’t touch any cells, ranges, sheets, or formulas. SaveCopyAs is a file-system operation, not a spreadsheet operation. Toggling ScreenUpdating or Calculation would add lines without adding value. The MsgBox at the end provides all the feedback needed.

#The filename in the message box

The MsgBox uses Dir(backupPath) instead of the full path. Dir() extracts just the filename from a qualified path:

MsgBox "Backup saved as:" & vbCrLf & vbCrLf & _
       Dir(backupPath), vbInformation, "Timestamped Backup"

C:\Users\jsmith\Documents\Tax\Clients\Henderson\Henderson-1120S_2026-12-15_1445.xlsm becomes just Henderson-1120S_2026-12-15_1445.xlsm in the message. The user knows where the file went — they don’t need the full UNC path in the dialog.

#What happens if you run it twice in the same minute

The timestamp goes down to the minute (_hhmm), not the second. If you run the macro at 2:45:01 PM and again at 2:45:59 PM, both backups get the same filename (_1445). The second SaveCopyAs silently overwrites the first.

This is by design — the most common “run twice in a minute” scenario is “I forgot whether I ran the backup and I want to be sure, so I’ll run it again.” Overwriting the same filename with an identical copy is the least harmful outcome. But if you need per-second granularity, change the format:

Const TIME_FORMAT As String = "yyyy-mm-dd_hhmmss"

The ss adds seconds to the timestamp — _144531 for 2:45:31 PM.

#Adapt It