Macro to backup Excel file before saving
Place the following code in the ThisWorkbook module of an Macro enabled Excel file (Alt-F11)
https://stackoverflow.com/questions/15267796/macro-to-make-a-backup-while-saving-a-file
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Application.EnableEvents = False
thisPath = ThisWorkbook.Path
myName = Left(ThisWorkbook.Name, (InStrRev(ThisWorkbook.Name, ".") – 1))
ext = Right(ThisWorkbook.Name, Len(ThisWorkbook.Name) – InStrRev(ThisWorkbook.Name, "."))
'backupdirectory = myName & " backups"
backupdirectory = "backup"
Set FSO = CreateObject("Scripting.FileSystemObject")
If Not FSO.FolderExists(ThisWorkbook.Path & "/" & backupdirectory) Then
FSO.CreateFolder (ThisWorkbook.Path & "/" & backupdirectory)
End If
T = Format(Now, "mmm dd yyyy hh mm ss")
ThisWorkbook.SaveCopyAs thisPath & "\" & backupdirectory & "\" & myName & " " & T & "." & ext
Application.EnableEvents = True
End Sub