I work on the macros frequently so don't think publishing an add-in is the right way to go.
I found this code online that will save a copy of the personal.xlsb file each time I save it, but it is failing on me with a compile error on the .SaveCopyAs sBak & Format(Now(), "_yyyymmdd.bak") line
Code: Select all
Option Explicit
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
'use Before Save event as Before Close event will not be triggered by Personal
'location to backup Personal.XLSB to (network drive, etc.)
Const sBak As String = "\\fake_network_Drive\Macro_Backup"
Application.DisplayAlerts = False
With Workbooks("Personal.xlsb")
.SaveCopyAs sBak & Format(Now(), "_yyyymmdd.bak")
.Save
End With
Application.DisplayAlerts = True
End Sub
I am open to other suggestions as well as I always value the opinions of fellow Loungers.
Greg