Save a copy solution

[dragon]

I had similar problem recently. See thread http:\vbaexpress.com/forum/showthread.php?t=14614

The following worked for me in the end...(change the location to your archive folder)

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) 
        Dim sFile 
        Dim location As String 
    Application.EnableEvents = False 
    location = "c:\log\staff authorisations archive\" 
    sFile = Replace(ThisWorkbook.Name, ".xls", " Backup ") & Format(Now, "yyyymmdd hh-mm-ss") 
    ThisWorkbook.SaveCopyAs location & sFile & ".xls" 
    Application.EnableEvents = True 
End Sub