Macro 15: Create a Backup of a Current Workbook with Today’s Date
We all know that making backups of your work is important. Now you can have a 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 it works
The trick to this macro is piecing together the new filename. The new filename has three pieces: 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.
You’ll notice that we are formatting the date (Format(Date, “mm-dd-yy”)). This is because by
default, the Date function returns mm/dd/yyyy. We use hyphens instead of forward slashes because
the forward slashes would cause the file save to fail. (Windows does not allow forward slashes in
filenames.)
The last piece of the new filename is the original filename. We use the Name property of the
ThisWorkbook object to capture that:
Sub Macro15()
‘Step 1: Save workbook with new filename
ThisWorkbook.SaveCopyAs _
Filename:=ThisWorkbook.Path & “\” & _
Format(Date, “mm-dd-yy”) & ” ” & _
ThisWorkbook.Name
End Sub
In the one and only step, the macro builds a new filename and uses the SaveCopyAs method to
save the file.
How to use it
To implement this macro, you can copy and paste it into a standard module:
1. Activate the Visual Basic Editor by pressing ALT+F11.
2. Right-click the project/workbook name in the Project window.
3. Choose Insert➜Module.
4. Type or paste the code in the newly created module.
