This assumes the workbook will be open at TimeToSave every week day
Option Explicit
Public Sub SaveOnTime()
'Runs CopyBookToFolder at SaveTime
Application.OnTime SaveTime, "CopyBookToFolder"
End Sub
Private Function SaveTime()
'Only called monday thru Friday
'Sets the date and time to save
Const TimeToSave = "16:30:00" 'Edit time to suit. Required to prevent Time part from creeping
Dim DayToSave As Date
'Moves Day to tommorow, except On Friday, moves it to Monday
If Weekday(Date) >= vbMonday And Weekday(Date) <= vbThursday Then
DayToSave = DateValue(DateAdd("d", 1, Date))
Else
DayToSave = DateValue(DateAdd("d", 3, Date))
End If
SaveTime = DayToSave + TimeValue(TimeToSave)
End Function
Private Sub CopyBookToFolder()
'Edit Path and Extension to suit. Adds Date (Saved) stamp to book Name
Me.SaveCopyAs "C:\TEMP\" & Me.Name & " - " & Format(Date, "yyyy/mm/dd") & "xlsm"
End Sub