Undo operation on a macro VBA

[zazass8]

Hello,

I have already created a macro to do some operations on a spreadsheet, and I also want to be able bring this spreadsheet back to its original format and restore all changes (UNDO).

I do know, that in Excel you can't undo a Macro directly so probably I was thinking if there's an alternative solution that I can write code to create this Undo Macro.

I was initially thinking of "reverse engineering" the original code from the first Macro.

Although, somewhere online I found an alternative solution that does it in a way where it stores the original spreadsheet in a variable, then by calling a Boolean if it's true to return the variable that we have stored our original spreadsheet. And this was coded in the code of our original Macro. But I am not sure on how to use it within the first macro, or if it's necessary to create a separate macro to complete my task. If this is the case, do I have to store the variable of the spreadsheet as a global variable?

What should I do about this?

 

[zazass8]

Hi, I think this code overcomplicates it a bit what I am actually trying to do. The backup method isn't really helpful when it is trying to rename the sheet name and hide it as well. All I want to do, is to create a copy of the original sheet at my first sub. Then call a separate sub method (that will do the UNDO operation) to fetch the copy from the first sub, then do the undo inside the other sub separately. How would I do that?

 

[MCLIFTO8]

Hi, I think this code overcomplicates it a bit what I am actually trying to do. The backup method isn't really helpful when it is trying to rename the sheet name and hide it as well.

It's renaming the copy we made so instead of (1) it now has "_original" which is a more meaningful name. It hides that copy so you don't have to look at it.

All I want to do, is to create a copy of the original sheet at my first sub.
Then call a separate sub method (that will do the UNDO operation) to fetch the copy from the first sub, then do the undo inside the other sub separately.

Ok, so if I'm understanding you, the issue is you don't want one sub to do two different functions with arguments. You want two completely separate subs, this can be done. However avoiding copying the sheet to instead "fetch the copy from the first sub" is a seperate ask. The only other method of backing up a sheet I know of is saving it to a variable and creating a new sheet based on that object.

However that's beyond my ability at the moment, you will have to ask someone else for help. Take care.

 

[zazass8]

Okay, so I tried using your function and instead of creating the copy of the original worksheet, it was renaming and hiding that original worksheet. Then, while trying to run my macro it wasn't able to detect that worksheet to run that macro on (RunTime Error '9'). Although, it is clear from your code lines of the backupSheet sub that you are creating that copy separately, meaning that I should have 'Sheet 1' and 'Sheet 1_original'. I am only having 'Sheet 1_original' that is also hidden. If I make both of these worksheets exist in the workbook, I believe my macro will be able to work.

 

[MCLIFTO8]

Put an "Exit Sub" in the line above "ErrB:"

Sub backupSheet(strSheetName As String, boolBackup As Boolean)
    'simple macro to copy a sheet, rename it and hide it.
    'also retores the original sheet
    'call this with
    'backupSheet "Sheet1", 1
    'to backup and
    'backupSheet "Sheet1", 0
    'to restore
    
    Dim strNewSheetName
    
    Application.DisplayAlerts = False
    On Error GoTo ErrB
    strNewSheetName = strSheetName & "_original"
    If boolBackup Then
    
        'backup sheet
        ThisWorkbook.Sheets(strSheetName).Copy Before:=Sheets(1)
        On Error GoTo ErrB
        Sheets(1).Name = strNewSheetName
        ThisWorkbook.Sheets(strNewSheetName).Visible = xlSheetHidden
        
    Else
        'restore sheet
        On Error GoTo ErrR
        ThisWorkbook.Sheets(strNewSheetName).Visible = xlSheetVisible
        ThisWorkbook.Sheets(strSheetName).Delete
        ThisWorkbook.Sheets(strNewSheetName).Name = strSheetName
        Application.DisplayAlerts = True
        Exit Sub
    End If
    Exit Sub
ErrB:
    ThisWorkbook.Sheets(strNewSheetName).Delete
    Sheets(1).Name = strNewSheetName
    ThisWorkbook.Sheets(strNewSheetName).Visible = xlSheetHidden
    Exit Sub
ErrR:
    Application.DisplayAlerts = True
    MsgBox "Cannot restore sheet that has not been backed up", vbExclamation, "Warning"
End Sub

'You don't need these subs they're just here to illustrate how you would call them in your macro.
Sub backup()
    backupSheet "Sheet1", 1
End Sub

Sub restore()
    backupSheet "Sheet1", 0
End Sub

 

[zazass8]

Put an "Exit Sub" in the line above "ErrB:"

Sub backupSheet(strSheetName As String, boolBackup As Boolean)
    'simple macro to copy a sheet, rename it and hide it.
    'also retores the original sheet
    'call this with
    'backupSheet "Sheet1", 1
    'to backup and
    'backupSheet "Sheet1", 0
    'to restore
   
    Dim strNewSheetName
   
    Application.DisplayAlerts = False
    On Error GoTo ErrB
    strNewSheetName = strSheetName & "_original"
    If boolBackup Then
   
        'backup sheet
        ThisWorkbook.Sheets(strSheetName).Copy Before:=Sheets(1)
        On Error GoTo ErrB
        Sheets(1).Name = strNewSheetName
        ThisWorkbook.Sheets(strNewSheetName).Visible = xlSheetHidden
       
    Else
        'restore sheet
        On Error GoTo ErrR
        ThisWorkbook.Sheets(strNewSheetName).Visible = xlSheetVisible
        ThisWorkbook.Sheets(strSheetName).Delete
        ThisWorkbook.Sheets(strNewSheetName).Name = strSheetName
        Application.DisplayAlerts = True
        Exit Sub
    End If
    Exit Sub
ErrB:
    ThisWorkbook.Sheets(strNewSheetName).Delete
    Sheets(1).Name = strNewSheetName
    ThisWorkbook.Sheets(strNewSheetName).Visible = xlSheetHidden
    Exit Sub
ErrR:
    Application.DisplayAlerts = True
    MsgBox "Cannot restore sheet that has not been backed up", vbExclamation, "Warning"
End Sub

'You don't need these subs they're just here to illustrate how you would call them in your macro.
Sub backup()
    backupSheet "Sheet1", 1
End Sub

Sub restore()
    backupSheet "Sheet1", 0
End Sub

Works perfectly now. Thank you very much and I appreciate your time and effort for this! It was really helpful.