Move 'old' rows to different workbook

[BethieBCB]

I created a spreadsheet that tracks modifications made to hospital accounts. Once an account has been corrected, the user clicks a command button and cell U in the active row fills with the current date and time ((now)) according to a macro I created.

After 5 business days of this cell being populated, I would like that account to "fall off" the spreadsheet and move to another workbook. How do I create a macro to check column "U" for every filled row to see if the current date and time is 5 days past the date / time stamp in that cell? And for the cells in column "U" that do have a 5-day difference, how do I move those rows to a different workbook (and not leave a blank row in my spreadsheet)?

I would love it if this macro could run automatically once or twice a day, but would be content with having to manually start it somehow if necessary.

 

[jeffreybrown]

BethieBCB,

I'm still plugging along with trying to grasp VBA so the date aspect I'm not sure but will work on it. For now if you don't mind using a helper column this will work. In column V when you type "done" this macro will cut the row and move it to worksheet Projects_Completed. You can change that title to fit your needs. I believe this code is courtesy VoG.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim LR As Long
If Target.Count > 1 Then Exit Sub
If Target.Column = 22 And LCase(Target.Value) = "done" Then
    Application.EnableEvents = False
    With Sheets("Projects_Completed")
        LR = .Range("A" & Rows.Count).End(xlUp).Row
        Target.EntireRow.Copy Destination:=.Range("A" & LR + 1)
        Target.EntireRow.Delete
        Application.CutCopyMode = False
        Application.EnableEvents = True
    End With
End If
End Sub