snaplink22
Board Regular
- Joined
- Jul 6, 2018
- Messages
- 129
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.
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.