Auto move row into another sheet without having to click Macro Run

[Gryder]

Hello Everyone,

I have a code (that when Run from the Marco) below that works great for moving the row into another sheet when the word "Due" is seen in Column G.

Is there a way for it to automatically move itself without any interaction once the word Due appears.

I have some script in Column G that works off the date in Column E. =IF(E5<(TODAY()-30),"DUE","Not Due")

Once the date changes the word from "Not Due" to "Due", I would like the row to move into another sheet (named archive engagement).

Any help is appreciated, Thanks, Garrett.

Macro script is below.

Sub Filter_Me_Please()
'Modified 7/30/2019 1:34:32 PM EDT
Application.ScreenUpdating = False
Sheets("current engagement").Activate
Dim Lastrow As Long
Dim Lastrowa As Long
Lastrowa = Sheets("archive engagement").Cells(Rows.Count, "B").End(xlUp).row + 1
Dim c As Long
Dim s As Variant
c = 7 ' Column Number Modify this to your need
s = "DUE" 'Search Value Modify to your need
Lastrow = Cells(Rows.Count, c).End(xlUp).row
With ActiveSheet.Cells(1, c).Resize(Lastrow)
.AutoFilter 1, s
counter = .Columns(c).SpecialCells(xlCellTypeVisible).Count
If counter > 1 Then
.Offset(1).Resize(.Rows.Count - 1).SpecialCells(xlCellTypeVisible).EntireRow.Copy Sheets("archive engagement").Rows(Lastrowa)
.Offset(1).Resize(.Rows.Count - 1).SpecialCells(xlCellTypeVisible).EntireRow.Delete

Else
MsgBox "No values found"
End If
.AutoFilter
End With
Application.ScreenUpdating = True
End Sub

 

[Norie]

You could call Filter_Me_Please from the workbook's Open event.

Private Sub Workbook_Open()
    Call  Filter_Me_Please 
End Sub

 

[Gryder]

Not sure where I would put that, at the top or bottom of script

 

[Norie]

It would go in the ThisWorkbook mopdule.

 

[Gryder]

Oh ok, that worked. so I have 5 different tabs that I want all the "Due" items to go into one Tab. I will have to place all 5 tabs in 5 different modules under ThisWorkBook?

 

[Norie]

Do you code for each tab?