VBA MOVE row with checkbox check in column "N" to "COMPLETED" sheet

[nathanw90]

Hello all,

Brand new to this in Excel, but have got this far!

I've been able to make the data copy to the new sheet when the box is checked, but but I cannot figure out how to make the row delete from the original sheet.

Here's the code I'm using currently...

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("N:N")) Is Nothing Then
If Target.Value = "True" Then
Dim COMPLETED As Worksheet
Set COMPLETED = ThisWorkbook.Sheets("COMPLETED")
Rows(Target.Row).Copy Destination:=COMPLETED.Cells(COMPLETED.Rows.Count, 1).End(xlUp).Offset(1, 0)

End If
End If
End Sub

 

[nathanw90]

Okay,

So I got it working, but I want this to run constantly. How to I make this macro run all the time?

 

[John_w]

Try this:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("N:N")) Is Nothing Then
        If Target.Value = "True" Then
            Dim COMPLETED As Worksheet
            Set COMPLETED = ThisWorkbook.Sheets("COMPLETED")
            Application.EnableEvents = False
            Rows(Target.Row).Copy Destination:=COMPLETED.Cells(COMPLETED.Rows.Count, 1).End(xlUp).Offset(1, 0)
            Rows(Target.Row).Delete
            Application.EnableEvents = True
        End If
    End If
End Sub