Sub Button2_Click()
Dim ws As Worksheet
Dim Cell As Object
Dim Dates As Range
Dim lastCell, DateTime, Yesterday As Double
Me.Range("B3:F" & Rows.Count).ClearContents
For Each ws In Worksheets
If ws.Range("B3") = "Date" Then
lastCell = ws.Cells(Rows.Count, "B").End(xlUp).Row
Set Dates = ws.Range("B4:B" & lastCell)
For Each Cell In Dates
DateTime = Cell + Cell.Offset(, 1)
Yesterday = Range("C1") - 1
If DateTime >= Yesterday Then
Me.Range("B" & Rows.Count).End(xlUp).Offset(1).Value = Cell.Value + Cell.Offset(, 1).Value
Me.Range("C" & Rows.Count).End(xlUp).Offset(1).Value = ws.Range("D2")
Me.Range("D" & Rows.Count).End(xlUp).Offset(1).Value = Cell.Offset(, 2).Value
Me.Range("E" & Rows.Count).End(xlUp).Offset(1).Value = Cell.Offset(, 3).Value
Me.Range("F" & Rows.Count).End(xlUp).Offset(1).Value = Cell.Offset(, 4).Value
End If
Next Cell
End If
Next ws
lastCell = Cells(Rows.Count, "B").End(xlUp).Row
Set Dates = Range("B3:F" & lastCell)
Dates.Sort Key1:=Range("B3")
Beep
End Sub
Bookmarks