So after researching it looks like I may have the wrong workbook event, but am unsure how to change the code to accomodate a different event.
Right now I have a 'Status' field in column H that is derived by comparing today's date to dates in columns E & F. The macro below is setup to move 'Completed' items to the 'Completed' sheet, and move all other items ('Planned', 'Unplanned','In Progress') to the 'Current State' sheet. Unfortunately, in order to trigger the macro I have to double click on the cell in column H and hit enter.
I'm actually wanting the macro to kickoff whenever the value in column H changes (even if it is the result of a change to column E or F. I'm thinking maybe I should have the WorksheetCalculate event, but as mentioned above.
I'd also like to autosort the data as the current code moves the active row to the bottom anytime the macro is triggered.
In advance, I very much appreciate the time.
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
' Code goes in the Worksheet specific module
Dim rng As Range
' Set Target Range
Set rng = Target.Parent.Range("H4:H500")
' Only look at single cell change
If Target.Count > 1 Then Exit Sub
' Only look at that range
If Intersect(Target, rng) Is Nothing Then Exit Sub
' Action if Condition(s) are met
Select Case Target.Text
Case "COMPLETED"
Target.EntireRow.Cut Sheets("Completed").Cells(Rows.Count, "A").End(xlUp).Offset(0)
Case "UNPLANNED"
Target.EntireRow.Cut Sheets("Current State").Cells(Rows.Count, "A").End(xlUp).Offset(1)
Case "PLANNED"
Target.EntireRow.Cut Sheets("Current State").Cells(Rows.Count, "A").End(xlUp).Offset(1)
Case "IN PROGRESS"
Target.EntireRow.Cut Sheets("Current State").Cells(Rows.Count, "A").End(xlUp).Offset(1)
End Select
End Sub
Thanks for the info
Bookmarks