Save A Backup Copy of Workbook in Excel 2007, 2010 and 2013

Never take a chance while working on your master data. We have best Excel backup plan for your essential data. You can use our Add-In to backup your data time to time by just pressing a short-cut key. Download Add in and use it with any excel file. (Click here to view how to add new Add-In to Excel)

  • Backup a new copy of your current data with file name and date time of backup
  • Will create a folder at first run
  • Will not replace any old backup file
  • Once Add-In added, a shortcut Crl+Q will work for every excel file in your system
  • Will save all sheets in the workbook
  • Save the backup at specified location / drive

You are done now, no need to enable macro every time. A simple shortcut key: Crl + Q and your backup is there at C:\excelbackup\. You will see a dialog box with backup confirmation.

If you are comfortable with VBA, just paste the below code in VBA console window and change the destination path accordingly. Macro 1 creates a new folder in given location, if not exist and will call Excel save function (Macro 2) to make a backup copy of current workbook.

Macro 1:

Public Const backupfolder As String = "C:\excelbackup\"
Sub Auto_Save()
If Dir(backupfolder, vbDirectory) = "" Then
' This will check if folder already exist
' If not then it will create new directory at first time
MkDir backupfolder
Call Save
' Save copy of Excel file to backupfolder
Else Call Save
' If folder already exist then only save copy of Excel File
End If
End Sub

Macro 2:

Function Save() As String
' Auto_save Macro
' This will create a copy of file at Seleted destination path
' Keyboard Shortcut: Ctrl+r '
'Saves the current file to a backup folder
Dim savedate
savedate = Date ' Current system date
Dim savetime
savetime = Time ' Current system time
Dim formattime As String
formattime = Format(savetime, "hh.mm.ss")
Dim formatdate As String
formatdate = Format(savedate, "DD-MM-YYYY")
Application.DisplayAlerts = False
Application.Run ("Auto_Save")
ActiveWorkbook.SaveCopyAs Filename:=backupfolder & formatdate & " " & formattime & " " & ActiveWorkbook.Name
ActiveWorkbook.Save
Application.DisplayAlerts = True
MsgBox "Backup Run. Please Check at: " & backupfolder

Hope it helps.

Was this article helpful?
YesNo