Excel VBA Delete after timer

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

---

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

---

Code:

---

Sub DelayMacro()


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


End Sub

---

Also asked this question on:
https://www.ozgrid.com/forum/forum/h...te-after-timer
https://stackoverflow.com/questions/...te-after-timer

Try something along these lines:

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
myName = "Range" & Timer 'invent a random-ish name
Sheets("Cache").Range("A" & NoOfCrew).Resize(10, 6).Name = myName 'give the range that's just been copied to that name
Application.CutCopyMode = False
DelayMacro myName 'pass that name onto the DelayMacro macro
End Sub

Sub Delete(theRange)
Range(theRange).Delete shift:=xlUp 'deletes the range itself
Names(theRange).Delete 'deletes the Name
End Sub

Sub DelayMacro(myStr)
Application.OnTime Now() + TimeValue("00:00:10"), "'Delete """ & myStr & """'" 'sets up a timed call of the Delete macro passing the name of the named range so it knows what to delete
End Sub

---

Note that since posting, I added a line in the Delete macro to prevent the build up of invalid Names (named ranges):

Code:

---

Names(theRange).Delete 'deletes the Name

---

I'm wondering what range.pastespecial without parameters does; is it the same as a plain copy?
If so then:

Code:

---

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

---

can be shortened to:

Code:

---

NoOfCrew = Sheets("Cache").Cells(Rows.Count, "A").End(xlUp).Row + 1
Sheets("Hotel Booking").Range("Q10:U19,X10:X19").Copy Sheets("Cache").Range("A" & NoOfCrew)

---

hey, i tried your code but i still have the same problem. when i enter a different number of people the second time and run the function before the first set of information is deleted, the deleting function will mess up. Using your code, it will delete all the information on the "Cache" sheet. I want it to be such that if I enter, for example set "A" of information, I want to be able to enter set "B" too, even when set "A" hasn't been cleared yet. Then set "A" will get deleted 10 seconds after it has been entered and the same with set "B", not at the same time.

Quote:

---

Originally Posted by p45cal

I'm wondering what range.pastespecial without parameters does; is it the same as a plain copy?
If so then:

Code:

---

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

---

can be shortened to:

Code:

---

NoOfCrew = Sheets("Cache").Cells(Rows.Count, "A").End(xlUp).Row + 1
Sheets("Hotel Booking").Range("Q10:U19,X10:X19").Copy Sheets("Cache").Range("A" & NoOfCrew)

---

---

I just put mine as it is so that it is easier to read. Still a beginner, not much experience with VBA

Quote:

---

Originally Posted by quanziee

it will delete all the information on the "Cache" sheet.

---

It shouldn't delete all the information on the cache sheet - I tested this.

Quote:

---

Originally Posted by quanziee

I want it to be such that if I enter, for example set "A" of information, I want to be able to enter set "B" too, even when set "A" hasn't been cleared yet. Then set "A" will get deleted 10 seconds after it has been entered and the same with set "B", not at the same time.

---

That's exactly what I arranged to happen, and tested it.

I think I'd need to see a version of your workbook to see what's going wrong.

In the attached are your 2 sheets.
Go to the Cache sheet where you'll find a button. Click it once and data will be copied from the Hotel Booking sheet. Wait 10 seconds and that data should be deleted.
Next click the same button, but 3 times at intervals of about 3 seconds. I've added a line to increment the values of the range on the Hotel Booking sheet so that the ranges are identifiable on the Cache sheet. You should see the copied ranges appearing, then 10 seconds after each range was pasted, disappearing, in the order they were pasted.

I'm not sure what I'm doing wrong. Your code on your file works fine, but it starts doing weird things when I enter a different amount of information on "Hotel Booking". When I delete the information in "Hotel Booking" and run the macro, it will still paste the rows of information that I deleted. Also when I try to enter new rows of information below it, it will not paste that information onto "Cache". The number of information, the number of rows I should say, is not constant. It keeps changing everytime the macro is run. I need it to work such that 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. I attached my file below. Note that the range of information is only "Q10:U19, X10:X19", but sometimes information may not be filled all the way until row 19.

The problem arises because named ranges end up overlapping each other. Always 10 rows are being copied, but if the bottom few rows are blank, the Sheets("Cache").Cells(Rows.Count, "A").End(xlUp).Row is ignoring those when placing the next set of data on the Cache sheet.
My suggestion is to copy only those rows down to the last cell with data in cells Q10:Q19. This helps to ensure that there is always something in column A of the Cache sheet for Sheets("Cache").Cells(Rows.Count, "A").End(xlUp).Row to work on, and thus prevent overlapping range names.

See if the attached helps.

While on the subject of .End(xlup), and as an aside, I see you use it often in the Hotel Booking's sheet Worksheet_Change event handler. If the data in the vicinity of D23:H32 is entered manually, it's quite likely that data will end up on the wrong rows in Q10:AB19 and below.
Try entering a last name (column D) and press Enter, then reailise you spelt it wrongly, so go back and correct it. The problem's not confined to column D.
What if some of the data in the vicinity of C11:C19 is missing? Blank cells will be copied, and the next .End(xlup) will return the same cell.

It's safer not to update on the fly, but to add a button (like the ones you have) to update Q10:AB19 all at once, and not use .End(xlup) at all.