It is time again for some more #macromoday fun. Today I want to share with you a really useful Macro to that will schedule an automatic backup at whatever time you choose of your Excel file. If you have ever lost any Excel files or data then you know how useful this small piece of VBA code will be.
If you want more Excel and VBA tips then sign up to my monthly Newsletter where I share 3 Excel Tips on the first Wednesday of the month and receive my free Ebook, 30 Excel Tips.
If you want to see all of the blog posts in the Macro Mondays Series or the example worksheets you can do so by clicking on the links below.
What Does The Macro Do?
This Macro will automatically save a backup copy of your Excel file in a location, date and time that you specify. In this example we will schedule a backup of our WorkLog File when we close the file on a Friday. The Macro uses the Workbook_BeforeClose Event which occurs before the workbook closes. If the workbook has been changed, this event occurs before the user is asked to save changes.
How Does It Work?
FIRST. Open Visual Basic – by hitting F11 or Developer Tab – Visual Basic, and this Macro needs to go into the Workbook_BeforeClose event code window.
Step 1. Excel determines if the current day is Friday. This is achieved setting the value vbSunday which returns a value from 1 being Sunday through to 7 being Saturday. We are determining if the current day is 6 which is Friday. You can change this to 5 for Thrusday, 4 for Wednesday etc etc.
Step 2. If Excel determines that it is Friday then any screen alerts are turned off.
Step 3. Excel then saves a copy of the workbook in a location you specify, and appends the current date in the format MMDDYYYY so yo know what date the backup refers to.. You can amend this piece of code to change the locations of your backup files.
Step 4. Excel now reverts back to displaying screen alerts
Step 5. The routine ends.
Want To Copy The Code?
‘macro by howtoexcelatexcel.com
Private Sub Workbook_BeforeClose(Cancel As Boolean)
If Weekday(Now(), vbSunday) = 6 Then
Application.DisplayAlerts = False
Call ThisWorkbook.SaveAs(“FOLDER_PATH_TO_SAVE_TO\Product_Matrix_” & Format(Now(), “MMDDYYY”), xlWorkbookDefault)
Application.DisplayAlerts = True