Excel VBA Delete after timer

Question

I want to copy and paste a bunch of information from sheet A to sheet B and I want to delete the information from sheet B after a certain time frame. However, the macro should be able to run multiple times and subsequent information from sheet A should be pasted below the current information pasted on sheet B that has not been deleted yet. My current code can do this but I have a problem where by if I paste more information on sheet B the second time, the deleting function will mess up.

Copy and Paste Function:

Sub Cache()

Dim NoOfCrew As Long

NoOfCrew = Sheets("Cache").Cells(Rows.Count, "A").End(xlUp).Row
NoOfCrew = NoOfCrew + 1

Sheets("Hotel Booking").Range("Q10:U19").Copy
Sheets("Cache").Range("A" & NoOfCrew).PasteSpecial

Sheets("Hotel Booking").Range("X10:X19").Copy
Sheets("Cache").Range("F" & NoOfCrew).PasteSpecial

Application.CutCopyMode = False
Run "DelayMacro"
End Sub

Delete Function:

Sub Delete()

Dim NoOfCrew As Long

NoOfCrew = Sheets("Hotel Booking").Cells(Rows.Count, "Q").End(xlUp).Row
NoOfCrew = NoOfCrew - 8

Sheets("Cache").Range("A2:F" & NoOfCrew).Delete shift:=xlUp

End Sub

Delay Function:

Sub DelayMacro()

Application.OnTime Now() + TimeValue("00:00:10"), "Delete"

End Sub

Also asked this on:

https://www.ozgrid.com/forum/forum/help-forums/excel-vba-macros/1205362-excel-vba-delete-after-timer

http://www.vbaexpress.com/forum/showthread.php?63161-Excel-VBA-Delete-after-timer&p=381929#post381929

How robust do you need it to be? Does it need to keep track of the different areas after you close the workbook? Or will there be a cleanup routine when the Workbook is opened to clear the "Cache"? — Profex
The information on sheet "A" will be deleted using another macro after it has been pasted on to sheet "B". I don't think the function can work when the workbook is closed too. It's fine if the function can just work when the workbook is open. However if the function can work if the workbook is closed, that would be a bonus. The "Cache" will only be cleared using the macro above, nothing else. My problem is that once the information is cleared on sheet "A", the function will not work. — quantamz
In your delete function, why are you taking the NoOfCrew from the last row in the "Hotel Booking" sheet, and then using it in the "Cache" sheet? Also, do you want to have the delete function just delete a portion of the data, or the entire cache? — Profex
I realized my mistake and tried using the number of crew from the "Cache" sheet but it still does not work when I run the macro multiple times. I only want the delete function to delete what it copied and pasted onto the "Cache" sheet. eg, if I enter 3 rows of information on "Hotel Booking" and run the macro, and I then delete the information from "Hotel Booking" and insert 4 rows of different information and run the macro again, the macro should delete accordingly. First delete the initial 3 rows, then the 4 rows, according to when they were run. — quantamz

Profex Profex · Accepted Answer · 2018-07-12T15:58:32

You need to keep track of the number or rows that are copied each time, and perhaps the starting row...but as I type this I realize that the starting row isn't required and just complicates matters. Anyway, I already fixed it up with the starting row, so here it is...

Type CacheArea
    StartingRow As Long
    Rows As Long
End Type

Private PasteCache() As CacheArea, CacheCount As Long

Sub Cache()
Dim NoOfCrew As Long

    NoOfCrew = Sheets("Cache").Cells(Rows.Count, "A").End(xlUp).Row
    NoOfCrew = NoOfCrew + 1

    Sheets("Hotel Booking").Range("Q10:U19").Copy
    Sheets("Cache").Range("A" & NoOfCrew).PasteSpecial

    Sheets("Hotel Booking").Range("X10:X19").Copy
    Sheets("Cache").Range("F" & NoOfCrew).PasteSpecial

    ReDim Preserve PasteCache(CacheCount)
    With PasteCache(CacheCount)
        .StartingRow = NoOfCrew
        .Rows = Sheets("Hotel Booking").Range("X10:X19").Rows.Count
    End With
    CacheCount = CacheCount + 1

    Application.CutCopyMode = False
    Run "DelayMacro"
End Sub


Sub Delete()
Dim NoOfCrew As Long, Row As Long

    If CacheCount > 0 Then
        Row = PasteCache(0).StartingRow
        NoOfCrew = PasteCache(0).Rows
        Sheets("Cache").Range("A" & Row & ":F" & Row + NoOfCrew - 1).Delete shift:=xlUp
    End If

    ' Shift all the values up in the buffer 
    ' Not required if we don't use .StartingRow and just assume that you are deleting from the top
    For i = 1 To CacheCount - 1
        PasteCache(i - 1) = PasteCache(i)
        ' Adjust all the starting rows down by the number of rows deleted.
        PasteCache(i - 1).StartingRow = PasteCache(i - 1).StartingRow - Rows
    Next

    ' Remove the extra entry
    If CacheCount > 0 Then
        ReDim Preserve PasteCache(CacheCount - 1)
        CacheCount = CacheCount - 1
    End If
End Sub

Excel VBA Delete after timer

1 Answers