Move Rows to another sheet automatically once conditions are met

[darellparker80]

I’m using excel for an investigation tracker and I’m trying to figure out how to move rows once conditions are met. For example, all Open investigations are listed in Sheet 1. Once the investigations are Closed, I want those rows to be moved to Sheet 3. Also, investigations that are more than 10 days old, I want those rows moved to Sheet 2. Please help.



1.Open investigation listed in Sheet 1.

2.Once the investigation is closed, the row is moved to Sheet 3.

3. Investigations that are more than 10 days old, are moved to Sheet 2.

 

[SanjayGMusafir]

I’m using excel for an investigation tracker and I’m trying to figure out how to move rows once conditions are met. For example, all Open investigations are listed in Sheet 1. Once the investigations are Closed, I want those rows to be moved to Sheet 3. Also, investigations that are more than 10 days old, I want those rows moved to Sheet 2. Please help.



1.Open investigation listed in Sheet 1.

2.Once the investigation is closed, the row is moved to Sheet 3.

3. Investigations that are more than 10 days old, are moved to Sheet 2.

Since you are using Excel 365 - Add a helper column to Check age or status
Then on sheet 3 or Sheet Use Filter function to bring data as per your need

The challenge left would be that this way you can't move a table row only sort them according to their age/status on sheet 1

 

[bebo021999]

Assume sheet1 named "Active", sheet2 named "Aged", sheet3 named "Archive"
it required 2 worksheet subs:

1) One in for sheet "Active", in worksheet_change even, to fire any change in column C to "closed"
Right click on tab "Active" name, view code, then add below code into:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim lr&
If Intersect(Target, Columns(3)) Is Nothing Or Target.Count > 1 Then Exit Sub
lr = Worksheets("Archive").Cells(Rows.Count, "C").End(xlUp).Row
If LCase(Target.Value) = "closed" Then
    With Target.EntireRow
        .Copy Worksheets("Archive").Range("A" & lr + 1)
        .Delete Shift:=xlUp
    End With
End If
End Sub

Change "open" to "closed" to cut row into sheet "Archive"

2) the orther in module 1. Hit F5, or assign it to a button, to click when you want to run, to check the date in column A

Option Explicit
Sub checkdate()
Dim lr&, j&, k&, cell As Range, arr(1 To 100000, 1 To 5)
On Error Resume Next
With Worksheets("Active")
    lr = .Cells(Rows.Count, "A").End(xlUp).Row
    For Each cell In .Range("A1:A" & lr)
        If Date - cell > 10 Then
            k = k + 1
            For j = 1 To 5
                arr(k, j) = cell.Offset(0, j - 1).Value2
            Next
            cell.Value = "#N/A"
        End If
    Next
.Range("A1:A" & lr).SpecialCells(xlCellTypeConstants, xlErrors).EntireRow.Delete
End With
Worksheets("Aged").Range("A2:E10000").Delete
Worksheets("Aged").Range("A2").Resize(k, 5).Value = arr
End Sub