Consulting

Results 1 to 10 of 10

Thread: Excel VBA Delete after timer

  1. #1
    VBAX Regular
    Joined
    Jul 2018
    Posts
    20
    Location

    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.

    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
    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
    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
    Last edited by quanziee; 07-11-2018 at 09:14 AM.

  2. #2
    Knowledge Base Approver VBAX Wizard p45cal's Avatar
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,844
    Try something along these lines:
    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
    Last edited by p45cal; 07-11-2018 at 11:01 AM.
    p45cal
    Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

  3. #3
    Knowledge Base Approver VBAX Wizard p45cal's Avatar
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,844
    Note that since posting, I added a line in the Delete macro to prevent the build up of invalid Names (named ranges):
    Names(theRange).Delete 'deletes the Name
    p45cal
    Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

  4. #4
    Knowledge Base Approver VBAX Wizard p45cal's Avatar
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,844
    I'm wondering what range.pastespecial without parameters does; is it the same as a plain copy?
    If so then:
    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:
    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)
    p45cal
    Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

  5. #5
    VBAX Regular
    Joined
    Jul 2018
    Posts
    20
    Location
    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.

  6. #6
    VBAX Regular
    Joined
    Jul 2018
    Posts
    20
    Location
    Quote Originally Posted by p45cal View Post
    I'm wondering what range.pastespecial without parameters does; is it the same as a plain copy?
    If so then:
    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:
    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

  7. #7
    Knowledge Base Approver VBAX Wizard p45cal's Avatar
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,844
    Quote Originally Posted by quanziee View Post
    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 View Post
    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.
    p45cal
    Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

  8. #8
    Knowledge Base Approver VBAX Wizard p45cal's Avatar
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,844
    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.
    Attached Files Attached Files
    p45cal
    Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

  9. #9
    VBAX Regular
    Joined
    Jul 2018
    Posts
    20
    Location
    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.
    Attached Files Attached Files

  10. #10
    Knowledge Base Approver VBAX Wizard p45cal's Avatar
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,844
    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.
    Attached Files Attached Files
    p45cal
    Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

Tags for this Thread

Posting Permissions

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