It’s Monday so must be time for some #MacroMonday Excel fun. Today I want to share with you a really handy Macro that will save a backup copy of your workbook with today’s (or whatever date it is) date. How cool is that?. I use this a LOT.
Making backups of your Excel file should be part of your usual spreadsheet routine.
What Does The Macro Do?
This Macro will save your Excel workbook to a new file with today’s date as part of the filename. I love this, as I can find easily my different versions of my workbooks with ease in a folder.
How Does It Work?
There are a few clever pieces of this Macro, and they combine the filepath, today’s date and the original filename you have used for your workbook.
There is actually only one yes ONE step to this awesome Macro. Excited? let’s get started!….and code.
Starting The VBA Macro.
First, you will need to open the Visual Basic Editor. There are two ways to do this.
- Either by hitting ALT +F11 or
- Selecting the Developer Tab | Code Group | Visual Basic. Both methods have the same result.
If you do not see the Developer Tab you may need to enable it. Please watch my YouTube video on how to do this below.
Before you begin to write any code you need to decide where to store the code. You have a choice.
- To store your code either in your Personal Macro Workbook or
- Store it in your current workbook
What Is The Difference In The Locations?.
So, there is a difference. If you save the code in your Personal Macro workbook it will be available in any Excel workbooks. If you store it in the current workbook then use is restricted to that workbook. In this instance, I may want to reuse the code so I will store it in my Personal Macro Workbook.
When you select this option then Excel creates (if it is not already created) this workbook and saves the macro in that location. By default, this macro workbook is named Personal.xlsb. This is a really useful macro that I want to reuse over and over again to backup my excel workbooks. So, I will make sure to save this in my Personal Macro Workbook.
If you want more details on creating and updating your personal macro workbook then I recommend my blog posts below.
Creating and Updating Your Personal Macro Workbook
Why Is My Personal Macro Workbook Not Loading Automatically?
Create A Shortcut To Your Personal Excel Macro Workbook
So, I have inserted a New Module into my Personal Macro Workbook. Type Sub then the name of the Excel macro. In this example, I have named my Macro SaveCopyOfMyWorkbook. Excel will automatically insert the End Sub lines of code. We just need to write our code to create the backup copy in between these two lines of code.
Sub SaveCopyOfMyWorkbook()
End Sub
Writing The Macro To Save A Backup.
So we kick off the code with the file path being created. This is done using the Path property of ThisWorkbook object and today’s date is created by the use of the Date function.
ThisWorkbook.SaveCopyAs _
Filename:=ThisWorkbook.Path & "\" & _
Format(Date, "mm-dd-yy") & " " & _
ThisWorkbook.Name
We have to format the date with hyphens “-” as the default date is saved with backslashes. But if you have used Windows for any length of time you will know that you can’t use backslashes in filenames. That’s ok though we can reformat it – not a problem.
The final part of the new filename is it original filename you have used to save your Excel workbook. Here we use the Name property of the ThisWorkbook object to capture and backup the Excel workbook.
ThisWorkbook.Name
Ending The Macro.
Once the backup copy of your Excel workbook has been made the code ends. This instruction was already entered into the module for us when started the type the name of the Macro.
End Sub
Here is a copy of the code to paste into your Excel Module if you need it. You are all ready to save a backup copy of your Excel workbook.
Sub SaveCopyOfMyWorkbook()
ThisWorkbook.SaveCopyAs _
Filename:=ThisWorkbook.Path & "\" & _
Format(Date, "mm-dd-yy") & " " & _
ThisWorkbook.Name
End Sub