Create a Backup Using Macros In Excel

Have an important spreadsheet that requires frequent backups and snapshots? You can create a backup using Excel Macros with a single click! Make sure your Excel has the Developer Tab enabled following this tutorial. I explain how you can do this below step by step!

Key Takeaways

• Automate Workbook Backups – Macros can automatically create backup copies of your Excel files with a single click or on file save.

• Protect Against Data Loss – Regular backups prevent loss of important data due to accidental deletion, file corruption, or overwrites.

• Add Timestamps to Filenames – Including date and time in backup filenames helps organize multiple versions efficiently.

• Run Backups Seamlessly – Macros can work in the background or be triggered by events like opening or saving a file.

• Useful for Critical Spreadsheets – Ideal for dashboards, reports, and financial models that require frequent updates and version tracking.

Quick Overview

What does it do?

Creates a backup copy of the spreadsheet in the specified folder

Copy Source Code:

Sub CreateBackup()

'Create a backup copy on the specified folder with the date today included
'Remember to change the folder directory as well
ThisWorkbook.SaveCopyAs Filename:="C:\ChangeMe\" & Format(Date, "mmddyyyy") & "-" & ThisWorkbook.name

End Sub

Final Result: 

How to Create a Backup Using Macros In Excel

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!

 

 

Frequently Asked Questions

What is the main purpose of using a backup macro in Excel?
To automatically save a duplicate of your workbook and protect your data from accidental loss.

Can I control where the backup files are saved?
Yes, you can customize the macro to save backups to a specific folder or location.

Will the macro overwrite old backups?
Only if you don’t add unique elements (like timestamps) to the filename—otherwise, each backup is saved separately.

Is this backup process manual or automatic?
You can set it to run manually with a button or automatically during events like saving or closing the file.

Do I need to enable anything in Excel to run the macro?
Yes, macros must be enabled in your Excel settings for the backup to work properly.