Autofilter Conditional Delete and Copy

[koolgeex]

Wondering if there's a macro that can do this when i press a button.

1. In spreadsheet "Complete Backlog"
2. Autofilter on
3. Show rows where WIP Status (Column K) equals "Closed"
4. Cut rows
5. Paste into next available row of "Closed Jobs" spreadsheet. No overwrites.

Or you could switch step 5 and 6 so that the rows are copied over to "Closed Jobs" and then deleted from "Complete Backlog".

Ideas?

 

[pboltonchina]

Try this on a copy of your as it is untested

Sub Delete_Closed()
Application.ScreenUpdating = False
Sheets("Complete Backlog").Select
Dim LR As Long, i As Long
LR = Range("K" & Rows.count).End(xlUp).Row
For i = LR To 1 Step -1
If Range("K" & i).Value = "Closed" Then Rows(i).Cut Destination:=Sheets("Closed Jobs").Cells(Rows.count, "A").End(xlUp)
Next i
Application.ScreenUpdating = True
End Sub