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.
Code
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
Display More
Code
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
Display More
EDIT:
Since no one has answered this on this site yet and I'm short of time, I asked this question on these sites too:
http://www.vbaexpress.com/foru…timer&p=381929#post381929
https://stackoverflow.com/ques…el-vba-delete-after-timer