An Excel Macro to Create a Currently Dated Workbook Backup

By Michael Alexander

You know that backing up your work is important. Now you can have an Excel macro do it for you. This simple macro saves your workbook to a new file with today’s date as part of the name.

How the macro works

The trick to this macro is piecing together the new filename as the path, today’s date, and the original filename.

The path is captured by using the Path property of the ThisWorkbook object. Today’s date is grabbed with the Date function.

By default, the Date function returns mm/dd/yyyy. Forward slashes would cause the file save to fail, so you format the date using hyphens instead (Format(Date, “mm-dd-yy”)) because Windows does not allow forward slashes in filenames.

The last piece of the new filename is the original filename. You capture it by using the Name property of the ThisWorkbook object:

Sub Macro1()
'Step 1: Save workbook with new filename
ThisWorkbook.SaveCopyAs _
Filename:=ThisWorkbook.Path & " & _