ExcelBanter - Auto Save

Hi Michael

I use something similar for backing up a file "Every Hour"

I modified the code (Not tested) to what I hope will do a run once save,
then stop.

I'm fairly certain some of the more seasoned Guru's will correct any
mistakes I may have made.

Lines below that preceed with ( ' ) are purely for comment and instruction
and not part of the actual command code

HTH...

' Copy / Paste this at the top of the Module window

Option Explicit
Public RunTime

' Then Copy / Paste this just under

Sub StartTimer()

RunTime = #2:00:00 AM#

Application.OnTime RunTime, "BackFileUp", schedule:=True

End Sub

Sub StopTimer()

On Error Resume Next
Application.OnTime RunTime, "BackFileUp", schedule:=False

End Sub

Sub BackFileUp()

Dim BackupFilePath As String
Dim BackupFileExtStr As String
Dim BackupFileName As String
Dim myWB As Workbook

With Application
.ScreenUpdating = False
End With

myWB = ActiveWorkbook

BackupFilePath = "T:\YourNetwork\YourDirectory"
BackupFileName = myWB.Name & " - " & Format(Date, "mm-dd-yyyy")
BackupFileExtStr = ".xlsx": FileFormatNum = 51

StartTimer

With myWB
.SaveAs BackupFilePath & BackupFileName & BackupFileExtStr,
FileFormat:=FileFormatNum
End With

StopTimer

With Application
.ScreenUpdating = True
End With

End Sub

' In the Left Window Pane of the VB Editor you will see the Workbook Name in
bold
' Double Left-Click it
' In the Right Window Pane Copy / Paste these 2 Private Sub Routines

Private Sub Workbook_Open()
StartTimer
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
StopTimer
End Sub

Good luck

Regards
Mick