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

dpaton05

Well-known Member
Joined
Aug 14, 2018
Messages
2,352
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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:
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
 

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest
Simply record a macro of the quote sheet when completed to a new worksheet called "Backup"
 
Upvote 0
Do you mean to write a macro to make a copy of the sheet in a new sheet?
 
Upvote 0
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
 
Upvote 0
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.
 
Last edited:
Upvote 0
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.

Code:
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.
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
What code do I need to change so that the backup procedure copies to the Backup sheet instead of creating a new one?
 
Upvote 0
Actually, I need an entire copy of NPSS_quote_sheet in the backup
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,255
Members
448,556
Latest member
peterhess2002

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top