It is time again for some more #macromonday fun. Today I want to share with you a really useful Macro 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 for my monthly newsletter where I share 3 Excel Tips on the first Wednesday of the month and receive my free Ebook, 30 Excel Tips.
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.
How To Excel At Excel – Macro Mondays Blog Posts.
What Does The Macro Do And How Does It Work?.
This Macro will create an automatic backup of an Excel file. It saves 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.
Step 1. Open Visual Basic To Get Started.
There are a number of ways to do this.
- Hit ALT +F11 or
- Developer Tab – Visual Basic
Both have the same result. Now, you need to decide where you are going to write the Visual Basic code. There are two choices. In the current workbook you are working on, or your Personal Macro Workbook. So, what is the difference?. Sometimes an Excel macro may be used over and over again. For example, you might write a macro that will be used in multiple workbooks to format some cells. The same process over and over again. It would make sense in this instance to insert a module into your Personal Macro Workbook. This allows access to macro or code whenever the Excel application is open.
Now, this code is very specific to a particular workbook. The aim is to make a backup copy of the file which is triggered by the closing of the workbook. Therefore the code is store in the Workbook Event Workbook_BeforeClose. Makes Sense right?.
So, first double click on ThisWorkbook in the VBA Project Window. Select Workbook form the left hand drop down box. Then go ahead and select Workbook_BeforeClose(Cancel As Boolean). Excel will automatically enter the End Sub line of the code for you at this stage. All you need to do is write the rest of the code in between these two lines.
Private Sub Workbook_BeforeClose(Cancel As Boolean)
End Sub
Step 2. Is Today Friday?.
Excel determines if the current day is Friday. This is achieved by 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 and so on. If it is Friday then any screen alerts that may occur when the code is running are turned off.
If Weekday(Now(), vbSunday) = 6 Then
Application.DisplayAlerts = False
Step 3. Save A Copy Of The Workbook.
Excel then saves a copy of the workbook in a location you specify. The current date in the format MMDDYYYY is appended to the file name so you know what date the backup refers to. You can amend this piece of code to change the locations of your backup files.
Call ThisWorkbook.SaveAs("C\MyFile" & Format(Now(), "MMDDYYY"), xlWorkbookDefault)
Step 4. Screen Updating Is Turned Back On.
Excel now reverts back to displaying screen alerts. This reverses the action in Step 2.
Application.DisplayAlerts = True
Step 5. The Code Ends.
The code ends with the End Sub line of code. This already exists from Step 1 of the macro. Just go ahead and test your code. Every Friday when the file is closed, Excel will make a new backup for you with the current date. How cool is that right?.
Create A Macro To Schedule An Automatic Backup Of An Excel File.
'macro by howtoexcelatexcel.com
Private Sub Workbook_BeforeClose(Cancel As Boolean)
If Weekday(Now(), vbSunday) = 6 Then
Application.DisplayAlerts = False
Call ThisWorkbook.SaveAs("C\MyFile" & Format(Now(), "MMDDYYY"), xlWorkbookDefault)
Application.DisplayAlerts = True
End If
End Sub
Other Excel And Macro Tips You Might Find Useful.
Macro Mondays – How To Protect Your Excel VBA Code
Open All Workbook In A Directory – Macro.