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 toolbar (or QAT in Excel 2007).

The macro saves the 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.

The Macro Code

I store this code in my Personal.xls file, because that workbook is always open when I'm using Excel. In the code, you can change the Save directory to one that you prefer on your computer or network. I use C:\Backups\

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.

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.