Need help with a macro to move row to another worksheet

[dojilee]

I need help with a macro to move rows to different worksheets. For example, If I put in Delivered, I want the row moved to a "Delivered" worksheet. If I put in Declined, I want the row moved to a "Declined" worksheet. I have been able to successfully get one of these to work at a time, but I need both of these to work in a single Macro. Help anyone? Here's the code I used to move one of them.


Option Explicit
Dim Flag As Boolean
Private Sub Worksheet_Change(ByVal Target As Range)
Dim LR As Long
LR = Range("A" & Rows.Count).End(xlUp).Row

If Flag = True Then Exit Sub
If Not Intersect(Target, Range("B2:B100" & LR)) Is Nothing Then
If Target.Value = "Delivered" Then

LR = Sheets("DELIVERED").Range("A" & Rows.Count).End(xlUp).Row + 1
Target.EntireRow.Copy
Sheets("DELIVERED").Range("A" & LR).PasteSpecial
Flag = True
Target.EntireRow.Delete
End If
End If
Application.CutCopyMode = False
Flag = False
End Sub

/close

[mrice]

Something like...

Option Explicit
Dim Flag As Boolean
Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
Dim LR As Long
LR = Range("A" & Rows.Count).End(xlUp).Row

If Flag = True Then Exit Sub
If Not Intersect(Target, Range("B2:B100" & LR)) Is Nothing Then
    If Target.Value = "Delivered" Then
        LR = Sheets("DELIVERED").Range("A" & Rows.Count).End(xlUp).Row + 1
        Target.EntireRow.Copy
        Sheets("DELIVERED").Range("A" & LR).PasteSpecial
        Flag = True
        Target.EntireRow.Delete
    ElseIf Target.Value = "Declined" Then
        LR = Sheets("DECLINED").Range("A" & Rows.Count).End(xlUp).Row + 1
        Target.EntireRow.Copy
        Sheets("DECLINED").Range("A" & LR).PasteSpecial
        Flag = True
        Target.EntireRow.Delete
    End If
End If
Application.CutCopyMode = False
Flag = False
Application.EnableEvents = True
End Sub

I've changed the EnableEvents status so the code doesn't call itself while it's running.

[dojilee]

YOU ROCK!! Works awesome. Thank you!