Excel VBA help

cwenos97 

Place the following Change Event Code on 2020 Projects Sheet Module.

To do so, right click on the 2020 Projects Sheet Tab --> View Code and paste the code given below into the opened code window.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.CountLarge > 1 Then Exit Sub
Dim wsDest      As Worksheet
Dim r           As Long
Dim dlr         As Long

Application.ScreenUpdating = False
On Error GoTo Skip

'Setting the destination Worksheet
Set wsDest = Worksheets("Completed Projects")

'Assuming the header row is Row2, data starts from Row3 and the completed column is Column J
If Target.Column = 10 And Target.Row > 2 Then
    If Target = "Y" Then
        Application.EnableEvents = False
        r = Target.Row
        dlr = wsDest.Cells(Rows.Count, "J").End(xlUp).Row + 1
        With Range("A" & r & ":J" & r)
            .Copy wsDest.Range("A" & dlr)
            .Delete shift:=xlUp
        End With
    End If
End If

Skip:
Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub