I need a way of undoing a macro that deletes all lines

[dpaton05]

I have a spreadsheet with a table that is used for storing quotes that has a delete all table lines button. My supervisor needs a way to undo or restore if the delete all lines button is clicked. The quote could be up to 100 pages long, so re-entering all that information is just not practical. Can someone give me some ideas on how I could achieve this please as I thought of possibly saving a copy of the file before the delete all lines code is run but there may be a better way of doing this?


Here is the delete all lines code:

Sub cmdDeleteAllQuoteLines()
    'Deleting The Data In A Table
    Dim tbl As ListObject
    Dim cell As Range
    
    Set tbl = Sheets("NPSS_quote_sheet").ListObjects("npss_quote")
    'Delete all table rows except first row
    With tbl.DataBodyRange
        If .Rows.Count > 1 Then
            .Offset(1, 0).Resize(.Rows.Count - 1, .Columns.Count).Rows.Delete

        End If
        'Clear the contents, but not delete the formulas
        For Each cell In tbl.ListRows(1).Range.Cells
            If Not cell.HasFormula Then
                cell.Value = ""
            End If
        Next
    End With
        With ThisWorkbook.Worksheets("NPSS_quote_sheet")
            .ListObjects("npss_quote").DataBodyRange.Columns(13).Value = 1 - 0.1 * ActiveSheet.chkIncrease.Value
            .Rows(11).Font.Bold = False
        End With
    
    'ListObjects("NPSS_quote").ListColumns("10%Increase").DataBodyRange.Value = "1"
End Sub

Thanks,
Dave

 

[Michael M]

Simply record a macro of the quote sheet when completed to a new worksheet called "Backup"

 

[dpaton05]

Do you mean to write a macro to make a copy of the sheet in a new sheet?

 

[dpaton05]

I know what I am going to do. When the delete all lines button is clicked, it will run code before the delete all code is run that copies the worksheet to a hidden worksheet called backup and have a restore button that will copy the data back from the hidden worksheet.

Could you help me with the code please?

Thanks Michael,
Dave

 

[Michael M]

Simply record the steps you have just told me....then call that macro in the delete all lines macro.

If you have problems, post back with the recorded code.

 

[dpaton05]

So I now have a procedure that copies the sheet to a sheet called backup. I want a restore button that will copy the sheet backup back to the sheet NPSS_quote_sheet. This is my code that is run when I click on the button to create the backup.

Sub Backup()
    Dim newName As String, wb1 As Workbook, sh1 As Worksheet
    Dim sh2 As Worksheet
    
    Set wb1 = ThisWorkbook
    Set sh1 = wb1.Worksheets("NPSS_quote_sheet")
     
    On Error Resume Next
    newName = "Backup"
 
    If newName <> "" Then
        ActiveSheet.Copy After:=Worksheets(Sheets.Count)
        On Error Resume Next
        ActiveSheet.Name = newName
    End If
    sh1.Activate
End Sub

Could you help me with code that will copy the sheet backup and paste it over the contents of NPSS_quote_sheet please Michael.

I also wanted to delete the backup sheet when it is restored too.

Thanks.

 

[Michael M]

What range of the NPSS_quote_sheet do you want to go to backup
AND
why not simply have the backup sheet in place all the time rather than recreating it.
Then all you have to do is clear it after the data is copied back to the NPSS_quote_sheet

 

[dpaton05]

I want to backup the contents of the table npss_quote that is on the sheet NPSS_quote_sheet and have a button to restore it if needed. That is a good idea of clearing the sheet instead of deleting it.

 

[dpaton05]

What code do I need to change so that the backup procedure copies to the Backup sheet instead of creating a new one?

 

[dpaton05]

Actually, I need an entire copy of NPSS_quote_sheet in the backup