Hi there

I have the following macro which will move rows from one sheet to another depending on one of the cell value in the row being set to Closed.

I want to do 2 things with this macro

1. One of the cell value in the row inserts current date and time i.e. now().
I want to move any rows if the following condition is met i.e.
if cell b23 and downwards having older than now() having yesterday date and time etc and cell p23 and downwards having value = closed to move to sheet 2 on the same workbook

please advice


Sub MoveRows()
   Dim rngOrigin As Range, rngDest As Range
   Dim i, j As Integer
   
   i = 1: j = 1
   Set rngOrigin = Sheets("Sheet1").Range("C7")
   Set rngDest = Sheets("Sheet2").Range("A1").Offset(Application.WorksheetFunction.CountA(Sheets("Sheet2").Range("A:A")))
   
   Do While rngOrigin.Offset(i, 0).Value <> ""
       If rngOrigin.Offset(i, 0).Value = "Closed" Then
           rngOrigin.Offset(i, 0).EntireRow.Copy
           Sheets("Sheet2").Activate
           rngDest.Offset(j, 0).Select
           ActiveSheet.Paste
           Sheets("Sheet1").Activate
           rngOrigin.Offset(i, 0).EntireRow.Delete xlShiftUp
           j = j + 1
           i = i - 1
       End If
       i = i + 1
   Loop
   Application.CutCopyMode = False
End Sub