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.

 

[Fluff]

You need to swap these two lines round

Application.EnableEvents = False
 If Intersect(Target, Range("A:A")) Is Nothing Then Exit Sub

You will also need to re-enable events before it will work.

 

[snaplink22]

You need to swap these two lines round

Application.EnableEvents = False
If Intersect(Target, Range("A:A")) Is Nothing Then Exit Sub

You will also need to re-enable events before it will work.

How do I re-enable events? New to VBA/Macros.

 

[Fluff]

Just run this

sub Reset()
Application.EnableEvents = True
End Sub

 

[snaplink22]

Just run this

sub Reset()
Application.EnableEvents = True
End Sub

Err, how do I do that if I already have my previous code in there?

 

[Fluff]

Just put that sub in any module & run it. You should then be able to use your change event.

 

[snaplink22]

Just put that sub in any module & run it. You should then be able to use your change event.

Sorry I'm uber new to VBA coding. Here's a screen shot of what I have. The Reset code you gave me is in Module 1. Not sure how I run it.

 

[Fluff]

Just put the cursor in the code & hit F5

 

[snaplink22]

Just put the cursor in the code & hit F5

Ah gotcha. Would you happen to know what my code should be to move those lines back over to Sheet1 if I change it to anything else other than Completed?

 

[Fluff]

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