Make sure your Excel has the Developer Tab enabled following this tutorial.
I explain how you can do this below step by step!
What does it do?
Creates a backup copy of the spreadsheet in the specified folder
Copy Source Code:
</p> <p>Sub CreateBackup()</p> <p>'Create a backup copy on the specified folder with the date today included<br /> 'Remember to change the folder directory as well<br /> ThisWorkbook.SaveCopyAs Filename:=&quot;C:\ChangeMe\&quot; &amp; Format(Date, &quot;mmddyyyy&quot;) &amp; &quot;-&quot; &amp; ThisWorkbook.name</p> <p>End Sub</p> <p>
Final Result:
Exercise Workbook:
STEP 1: Go to Developer > Code > Visual Basic
STEP 2: Paste in your code and Select Save.
Make sure to change the directory into a folder that exists for you:
You can see we have changed this to C:\Data\
Close the window afterwards.
STEP 3: Let us test it out! You can see that we have no files yet in the directory:
Go to Developer > Code > Macros
Make sure your macro is selected. Click Run.
With just one click, you have generated a backup of your current workbook!
How to Create a Backup Using Macros In Excel
Bryan
Bryan Hong is an IT Software Developer for more than 10 years and has the following certifications: Microsoft Certified Professional Developer (MCPD): Web Developer, Microsoft Certified Technology Specialist (MCTS): Windows Applications, Microsoft Certified Systems Engineer (MCSE) and Microsoft Certified Systems Administrator (MCSA).
He is also an Amazon #1 bestselling author of 4 Microsoft Excel books and a teacher of Microsoft Excel & Office at the MyExecelOnline Academy Online Course.