Consulting

Results 1 to 10 of 10

Thread: Excel VBA Delete after timer

Threaded View

Previous Post Previous Post   Next Post Next Post
  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.

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
  •