VBA to move row based on one criteria onto another sheet

[Matt Young]

Hello there,
I realise this question has probably been asked (many times) before but I am really struggling to come up/ find anything that I fulfills my needs.
Basically I just need to be able to push a button that moves (not copies) an entire row (A:Q) onto another sheet (called "Closed") when the status in Column "N" is changed to closed, I don't want it be moved to new sheet each time but just keep adding rows to the "Closed" sheet . I have had varying levels of success but I have not got it right. Unfortunately I haven't got any of the code from these attempts. If any one could help me out It would be much appreciated.

 

[onlyadrafter]

Hello,

does this work as expected?

Sub MOVE_TO_CLOSED()
    Application.ScreenUpdating = False
    With Sheets("Reported Issues")
        For MY_ROWS = .Range("A" & .Rows.Count).End(xlUp).Row To 2 Step -1
            If UCase(.Range("N" & MY_ROWS).Value) = "CLOSED" Then
                .Rows(MY_ROWS).Copy
                Sheets("CLOSED").Range("A" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial (xlPasteAll)
                .Rows(MY_ROWS).Delete
            End If
        Next MY_ROWS
    End With
    Application.ScreenUpdating = True
End Sub

 

[mumps]

Copy and paste this macro into the worksheet code module. Do the following: right click the tab name for your sheet and click 'View Code'. Paste the macro into the empty code window that opens up. Close the code window to return to your sheet. Enter "Closed" in column N and press the RETURN key.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("N:N")) Is Nothing Then Exit Sub
    With Application
        .ScreenUpdating = False
        .EnableEvents = False
    End With
    If Target = "Closed" Then
        Target.EntireRow.Copy Sheets("Closed").Cells(Sheets("Closed").Rows.Count, "A").End(xlUp).Offset(1)
        Target.EntireRow.Delete
    End If
    With Application
        .ScreenUpdating = True
        .EnableEvents = True
    End With
End Sub