Private Sub Worksheet_Change(ByVal Target As Range)
Application.ScreenUpdating = False
Dim i As Integer
Dim b As Integer
Dim Lastrow As Long
Lastrow = Cells(Rows.Count, "A").End(xlUp).Row
Lastrowb = Sheets("Complete").Cells(Rows.Count, "A").End(xlUp).Row + 1
Lastrowc = Cells(Rows.Count, "A").End(xlUp).Row + 1
Sheets("Active").Activate
For i = 1 To Lastrow
If Cells(i, 18).Value = "COMPLETE" Then
Rows(i).Copy Destination:=Sheets("Complete").Rows(Lastrowb)
Lastrowb = Lastrowb + 1
End If
Next
For b = Lastrowc To 1 Step -1
If Cells(b, 18).Value = "COMPLETE" Then
Rows(b).EntireRow.Delete
End If
Next
Application.ScreenUpdating = True
Lastrow = Cells(Rows.Count, "A").End(xlUp).Row
Lastrowb = Sheets("Cancelled").Cells(Rows.Count, "A").End(xlUp).Row + 1
Lastrowc = Cells(Rows.Count, "A").End(xlUp).Row + 1
Sheets("Active").Activate
For i = 1 To Lastrow
If Cells(i, 18).Value = "CANCEL" Then
Rows(i).Copy Destination:=Sheets("Cancelled").Rows(Lastrowb)
Lastrowb = Lastrowb + 1
End If
Next
For b = Lastrowc To 1 Step -1
If Cells(b, 18).Value = "CANCEL" Then
Rows(b).EntireRow.Delete
End If
Next
Application.ScreenUpdating = True
With Sheets("Active")
Dim lr As Long
Set sh = ActiveSheet
Application.ScreenUpdating = True
Lastrow = Cells(Rows.Count, "A").End(xlUp).Row
Lastrowb = Sheets("Pending").Cells(Rows.Count, "A").End(xlUp).Row + 1
Lastrowc = Cells(Rows.Count, "A").End(xlUp).Row + 1
Sheets("Active").Activate
For i = 1 To Lastrow
If Cells(i, 18).Value = "PENDING" Then
Rows(i).Copy Destination:=Sheets("Pending").Rows(Lastrowb)
Lastrowb = Lastrowb + 1
End If
Next
For b = Lastrowc To 1 Step -1
If Cells(b, 18).Value = "PENDING" Then
Rows(b).EntireRow.Delete
End If
Next
Application.ScreenUpdating = True
End With
End Sub