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.
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 SubSub 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 SubAlso asked this question on:Sub DelayMacro() Application.OnTime Now() + TimeValue("00:00:10"), "Delete" End Sub
https://www.ozgrid.com/forum/forum/h...te-after-timer
https://stackoverflow.com/questions/...te-after-timer