Page 1 of 3 123 LastLast
Results 1 to 10 of 26

Thread: I need a way of undoing a macro that deletes all lines
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Aug 2018
    Location
    NSW, Australia
    Posts
    939
    Post Thanks / Like
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)

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

    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

  2. #2
    Board Regular Michael M's Avatar
    Join Date
    Oct 2005
    Location
    South Western NSW
    Posts
    17,806
    Post Thanks / Like
    Mentioned
    18 Post(s)
    Tagged
    2 Thread(s)

    Default Re: I need a way of undoing a macro that deletes all lines

    Simply record a macro of the quote sheet when completed to a new worksheet called "Backup"
    Regards
    Michael M
    ---------------------------------------
    The more I learn, the less I seem to know.....A Please and Thank You cost nothing !
    It's easier to debug if we can see the whole macro !
    Home 2007 & 2013

    - Posting guidelines, forum rules and terms of use

    - To download Mr Excel HTML Maker

    - Try searching for your answer first, see how

    - Read the FAQs

    - List of BB codes


    [CODE]Place Your Code Here[/CODE]

  3. #3
    Board Regular
    Join Date
    Aug 2018
    Location
    NSW, Australia
    Posts
    939
    Post Thanks / Like
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)

    Default Re: I need a way of undoing a macro that deletes all lines

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

  4. #4
    Board Regular
    Join Date
    Aug 2018
    Location
    NSW, Australia
    Posts
    939
    Post Thanks / Like
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)

    Default Re: I need a way of undoing a macro that deletes all lines

    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

  5. #5
    Board Regular Michael M's Avatar
    Join Date
    Oct 2005
    Location
    South Western NSW
    Posts
    17,806
    Post Thanks / Like
    Mentioned
    18 Post(s)
    Tagged
    2 Thread(s)

    Default Re: I need a way of undoing a macro that deletes all lines

    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 by Michael M; Aug 29th, 2019 at 08:47 PM.
    Regards
    Michael M
    ---------------------------------------
    The more I learn, the less I seem to know.....A Please and Thank You cost nothing !
    It's easier to debug if we can see the whole macro !
    Home 2007 & 2013

    - Posting guidelines, forum rules and terms of use

    - To download Mr Excel HTML Maker

    - Try searching for your answer first, see how

    - Read the FAQs

    - List of BB codes


    [CODE]Place Your Code Here[/CODE]

  6. #6
    Board Regular
    Join Date
    Aug 2018
    Location
    NSW, Australia
    Posts
    939
    Post Thanks / Like
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)

    Default Re: I need a way of undoing a macro that deletes all lines

    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.

  7. #7
    Board Regular Michael M's Avatar
    Join Date
    Oct 2005
    Location
    South Western NSW
    Posts
    17,806
    Post Thanks / Like
    Mentioned
    18 Post(s)
    Tagged
    2 Thread(s)

    Default Re: I need a way of undoing a macro that deletes all lines

    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
    Regards
    Michael M
    ---------------------------------------
    The more I learn, the less I seem to know.....A Please and Thank You cost nothing !
    It's easier to debug if we can see the whole macro !
    Home 2007 & 2013

    - Posting guidelines, forum rules and terms of use

    - To download Mr Excel HTML Maker

    - Try searching for your answer first, see how

    - Read the FAQs

    - List of BB codes


    [CODE]Place Your Code Here[/CODE]

  8. #8
    Board Regular
    Join Date
    Aug 2018
    Location
    NSW, Australia
    Posts
    939
    Post Thanks / Like
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)

    Default Re: I need a way of undoing a macro that deletes all lines

    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.

  9. #9
    Board Regular
    Join Date
    Aug 2018
    Location
    NSW, Australia
    Posts
    939
    Post Thanks / Like
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)

    Default Re: I need a way of undoing a macro that deletes all lines

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

  10. #10
    Board Regular
    Join Date
    Aug 2018
    Location
    NSW, Australia
    Posts
    939
    Post Thanks / Like
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)

    Default Re: I need a way of undoing a macro that deletes all lines

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

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •