VBA Code Help with Moving Rows

[snaplink22]

Hello,

I am attempting to move any of the rows in my first sheet to the second sheet when my drop down list changes from any other option to "Completed" in column A. Here is the code I have currently, but it's not working:

Private Sub Worksheet_Change(ByVal Target As Range)
Application.ScreenUpdating = False
Application.EnableEvents = False
If Intersect(Target, Range("A:A")) Is Nothing Then Exit Sub
On Error GoTo errhandler
If Target = "Completed" Then
Target.EntireRow.Copy Sheets("Sheet2").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)
Target.EntireRow.Delete
End If
Application.ScreenUpdating = True
errhandler:
Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub

If possible, I would also like to have that same option pull that row back to Sheet1 if any of the drop down options other than "Completed" is changed.

 

[snaplink22]

Because you have deleted the row, there is no way you can change Completed to anything else.

Hmm, I thought if I put Sheet!N1:N5 in my drop down list when it moved over to Sheet2 it would keep that list, but it doesn't appear to be the case. No worries though, I just can just copy/insert that row back over if I select Completed by mistake. Thank you very much Fluff for your help and walking me through this new and confusing process!

 

[Fluff]

You're welcome & thanks for the feedback.