Move 'old' rows to different workbook

BethieBCB

New Member
Joined
May 11, 2007
Messages
5
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.
 

Some videos you may like

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.

jeffreybrown

Well-known Member
Joined
Jul 28, 2004
Messages
5,145
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.

Code:
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
 

Watch MrExcel Video

Forum statistics

Threads
1,099,788
Messages
5,470,788
Members
406,721
Latest member
Laiceyshae

This Week's Hot Topics

Top