Этот макрос сохраняет текущую книгу в папку C:\TEMP, добавляя к имени книги текущее время и дату.

Sub Backup_Active_Workbook()
    Dim x As String
    strPath = "c:\TEMP"
    On Error Resume Next
    x = GetAttr(strPath) And 0
    If Err = 0 Then ' если путь существует - сохраняем копию книги
        strDate = Format(Now, "dd/mm/yy hh-mm")
        FileNameXls = strPath & "\" & Left(ActiveWorkbook.Name,  _
             Len(ActiveWorkbook.Name) - 4) & " " & strDate & ".xls"
        ActiveWorkbook.SaveCopyAs Filename:=FileNameXls
    Else 'если путь не существует - выводим сообщение
        MsgBox "Папка " & strPath & " недоступна или не существует!", vbCritical
    End If
End Sub

При желании можно заменить первую строку на:

Private Sub Workbook_BeforeClose(Cancel As Boolean)

и поместить этот макрос не в Module1 как обычно, а в модуль ЭтаКнига (ThisWorkbook) - тогда автоматическое сохранение резервной копии будет происходить каждый раз перед закрытием файла.