How do you save your file while working in Excel?
- Do you click the Save button, and save over the previous version?
- Do you use Excel’s AutoSave feature?
- Or the AutoSafe utility by Jan Karel Pieterse?
- Do you choose Save As, and save the file with a different name?
- Something else?
Macro to SaveAsCopy
I like to have different versions of a file, so I can go back to a previous version if something goes horribly wrong.
So, I created a macro to save my files, and added a button to the Quick Access Toolbar (QAT).
What the Macro Does
The macro saves a copy of the active file in a specified folder, adding the year, month, day, hour and minute to the file name.
For example, if the file I’m working on is named Budget2009.xls, the backup file would be named Budget2009_20081215_1008.xls if I saved it at 10:08 AM today.
NOTE: This macro does not make any changes to the active workbook, so it does NOT wipe out the Undo stack in my version of Excel. Test this on your own computer though, to make sure it’s the same for you!
The Backup Macro Code
Copy the macro below, and store it on a regular code module, in a workbook that is always open, such as the Personal Workbook.
In the code, you can change the Save directory to one that you prefer on your computer or network.
NOTE: I use C:\Backups\, but you could change that to another directory that you use.
Sub SaveBUCopy() Dim strFile As String Dim strName As String Dim lExt As Long Dim strDir As String Dim strExt As String strName = ActiveWorkbook.Name strDir = "C:\Backups\" If UCase(Right(strName, 4)) = ".XLS" Then lExt = 4 Else lExt = 5 End If strFile = Left(strName, Len(strName) - lExt) strExt = Right(strName, lExt) ActiveWorkbook.SaveCopyAs strDir & strFile _ & Format(Now, "_yyyymmdd_HhMm") & strExt End Sub
Add Button to QAT
After you’ve added the SaveBUCopy macro to your workbook, and changed the directory name, make the macro easy to run.
To do that, follow the steps in this video, to add your macro to the Quick Access Toolbar.
There are written steps on my Contextures website — Add a Macro to the QAT.
______________________________
I use AutoSafe from JKP…. its great
Thanks Sam. I use it too 🙂
I wrote a macro that saves a copy to the same folder structure on another drive (it creates the folders if not already there) and then does a normal save. That way I always have a recent backup to go to until the next time I use the macro. Aside from that, I just drag lots of copies in Windows Explorer (literally “Copy (2) of …xls) until I reach some kind of “no turning back” spot. Then there’s also the zip files and emails of previous versions.
Love your blog!
Keep in mind that using a macro to make a backup wipes out the undo information. This can be a problem with code that backs up the file automatically.
Consider this: You accidentally delete a large range of data. The backup macro kicks in, and destroys the undo stack so you can’t undo your mistake.
nice tip!
Thanks for the reminder, Sam and Jan Karel — I’ve added a link to AutoSafe in the article.
Doug, thanks for describing how you handle backups, and I’m glad you like the blog!
John, that’s a good point. My backup macro only runs when I click the button, so that gives me control over when to save. And since it saves to a different file, I could always close the current file without saving, to restore to the previously saved copy.
Thanks Nicolai — I hope some of these ideas helped you.
This sounds like a great feature, but can you give a step by step for people who are not fluent in excel language, ie. go to Macro’s, go to view,then type a name create, etc.
When I paste the code, the
strDir = “C:\Backups\”
line is red, and I get a syntax error. The folder does exist in this location. Any idea how to fis this?
Thanks, Renee, and the double quotes should be straight, not curly.
I’ve fixed the code in the article, so you can copy the revised macro, and it should work correctly.