Safeguard Your Work with a Backup Button in Your Worksheet

If your Excel worksheet contains important information and you need to make quick backups, then it might be useful to integrate a backup button into your worksheet. In this Excel Training video, we’ll show you how to make a backup button, which, when clicked, will duplicate your current worksheet and rename it with the date and time appended.

To follow along, you’ll need to enable the developer tab. But don’t worry — we’ll show you how to enable the developer tab in Windows and mac.

Resources

VBA Script:

Sub CreateBackupSheet()
    Dim originalSheet As Worksheet
    Dim newSheet As Worksheet
    Dim currentDate As String
    
    ' Get a reference to the active sheet (original sheet)
    Set originalSheet = ActiveSheet
    
    ' Get the current date and time
    currentDate = Format(Now, "mm-dd_h-m")
    
    ' Create a new sheet
    Set newSheet = Worksheets.Add(After:=originalSheet)
    
    ' Copy the content from the original sheet to the new sheet
    originalSheet.Cells.Copy newSheet.Cells
    
    ' Rename the new sheet
    newSheet.Name = originalSheet.Name & "(" & currentDate & ")"
    
    ' Clear the clipboard
    Application.CutCopyMode = False
End Sub