VBA code to move entire rows once date has passed

[cornflower13]

I have tried so many other suggestions from various internet sources but nothing seems to do what I need it to. I'm a fairly basic user so please be kind

I want my macro to move (not just copy) entire rows from a table on sheet 1 "DIARY" to a table on sheet 2 "HISTORICAL" once the date has passed. Today's booking should stay on sheet 1 until the day is over. The date is in column A.

I'd also like this to happen automatically if possible. So for example, every time the sheet is opened, everything from before today (but not including today) moves from the diary tab to the historical tab.

Please help

 

[Skyybot]

Can you post in XL2BB, or File Share your Workbook?

 

[kevin9999]

Please try the following on a copy of your workbook. Right click on the DIARY tab, select View Code, and place the code in the window that appears on the right of screen.

Private Sub Worksheet_Activate()
    Dim d As Date
    d = Format(Date - 1, "dd mmmm yyyy")
    Dim Lob1 As ListObject
    Set Lob1 = Me.ListObjects(1)
    Dim ws As Worksheet
    Set ws = Worksheets("HISTORICAL")
    Lob1.AutoFilter.ShowAllData
    With Lob1.Range
        .AutoFilter 1, "<=" & CLng(d)
        If WorksheetFunction.Subtotal(3, .Columns(1)) > 2 Then
            .Offset(1).Resize(.Rows.Count - 1).Copy ws.Cells(Rows.Count, 1).End(xlUp)
            .Offset(1).Resize(.Rows.Count - 1).EntireRow.Delete
        End If
    End With
    Lob1.AutoFilter.ShowAllData
End Sub