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.


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

John Gleave

John Gleave has been a researcher, content writer, and senior editor at Business Tech Planet since 2022. John was formerly a data analyst and web designer with expertise in several programming languages, such as JavaScript, JQuery, PHP, CSS, SQL, and more! With a passion for writing and technology, he has now focused his skills on crafting tech guides for BTP. You can connect with John on LinkedIn.

Recent Posts