How do I get back my original file after performing all the Operations on the excel file using VBA?

Question

Can Anyone tell me how do I undo all my changes to my workbook? I have file excel1.xlsx and I have did sorting and many operations on the excel.xlsx using vba. But at the end I want the excel1.xlsx to be the same which was at the start. How do i Undo all my changes using vba?

 activeworkbook.saved = True

I have found that it retains back all the contents as at the begginning but its not working.So is there any command where i can get back my original file after performing operations over it. Well yes

              wb1.Sheets(1).Activate
              ActiveWorkbook.Close savechanges:=False

It works but I dont want my workbooks to be closed it should be still opened. How do I make it? Thanks in advance.

Answer 1

In order to undo a sub routine, you can either choose not to save the file and just close it, or you have to write a special sub routine to save the state of the file, then restore the state (custom undo). This is one of the pains with sub routines is that they cannot be undone through normal undo. Most people, me including, will reccomend you work off a backup.

When making your custome undo routine, the big question is what do you need to save the state for? Saving all information about the file would be unnessesarily heavy, so it's good to know what you want to save.

Update: This is a dirty way to backup the sheet if you only have 1 sheet of data. This is more of a proof of concept of one way to create a backup and not finalized perfect code. It just creates a backup copy of the currentsheet and when you'restore' you are simply deleting the original sheet and renaming the backup to what it used to be called. :p

How to test: Put some data and value in your original sheet then run the Test() sub-routine!

Public backupSheet As Worksheet
Public originalSheet As Worksheet
Public originalSheetName As String

Sub CreateBackup()
    Set originalSheet = Application.ActiveSheet
    originalSheetName = originalSheet.Name
    originalSheet.Copy After:=Sheets(Application.Worksheets.Count)
    Set backupSheet = Application.ActiveSheet
    backupSheet.Name = "backup"
    originalSheet.Activate
End Sub

Sub RestoreBackup()
    Application.DisplayAlerts = False
    originalSheet.Delete
    backupSheet.Name = originalSheetName
    Application.DisplayAlerts = True
End Sub

Sub ZerosFromHell()
    Range("A1:Z100").Select
    Cells.Value = 0
End Sub

Sub Test()
    Call CreateBackup
    Call ZerosFromHell
    MsgBox "look at all those darn 0s!"
    Call RestoreBackup
End Sub