Macro to Create a Copy of the Workbook with a Time stamp

These VBA macros save a backup copy of the workbook with the date and time in the file name, such as 2026-10-05 1430 Sales.xlsm, while you keep working in the original. One version does it automatically every time you save.

Works inExcel 2010 and later, Windows and Mac
UndoNot possible. Save a copy first
Save asMacro-Enabled Workbook (.xlsm)

New to macros? See how to use the macros on this site: copy the code, paste it into a module with Alt + F11, and run it with Alt + F8.

Save a timestamped copy

Sub SaveTimestampCopy()
    Dim f As String
    If ActiveWorkbook.Path = "" Then MsgBox "Save the workbook first.": Exit Sub
    f = ActiveWorkbook.Path & Application.PathSeparator & _
        Format(Now, "yyyy-mm-dd hhmm") & " " & ActiveWorkbook.Name
    ActiveWorkbook.SaveCopyAs f
    MsgBox "Backup saved: " & f
End Sub

SaveCopyAs writes the copy without switching you to it, so you keep working in the original file.

Save backups in a Backups folder

Sub SaveToBackupsFolder()
    Dim p As String
    p = ActiveWorkbook.Path & Application.PathSeparator & "Backups"
    If Dir(p, vbDirectory) = "" Then MkDir p
    ActiveWorkbook.SaveCopyAs p & Application.PathSeparator & _
        Format(Now, "yyyy-mm-dd hhmm") & " " & ActiveWorkbook.Name
End Sub

Back up automatically on every save

Press Alt + F11, double-click ThisWorkbook in the Project pane, and paste:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    Dim p As String
    p = Me.Path & Application.PathSeparator & "Backups"
    If Me.Path = "" Then Exit Sub
    If Dir(p, vbDirectory) = "" Then MkDir p
    Me.SaveCopyAs p & Application.PathSeparator & Format(Now, "yyyy-mm-dd hhmm") & " " & Me.Name
End Sub

Each time you save, the previous version goes into the Backups folder first.

Things to know

  • Dates in yyyy-mm-dd order sort correctly in File Explorer.
  • Automatic backups add up. Delete old ones now and then.
  • OneDrive and SharePoint also keep version history: File → Info → Version History.

Related macros