Cut Cells Marked "Closed" and Paste to New Sheet

[koolgeex]

1 Workbook
2 Spreadsheets - "Complete Backlog" & "Closed Jobs"
If Column K in "Complete Backlog" says "Closed" then cut that row and paste it into the next available row of "Closed Jobs".

I have this code below but there is an error with it. It currently cuts all of the items marked closed but it doesn't paste all of them on the new sheet.
On my last go around, it cut 34 rows marked Closed and only pasted 20 rows into the new sheet. Suggestions?

Sub refresh()
Dim LR As Long, i As Long
With Sheets("Complete Backlog")
    LR = .Cells.Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Row
    For i = 1 To LR
        If .Range("K" & i).Value = "Closed" Then .Range("A" & i).Resize(, 11).Cut Destination:=Sheets("Closed Jobs").Range("A" & Rows.Count).End(xlUp).Offset(1)
    Next i
    .Range("A2:K" & LR).SpecialCells(xlCellTypeBlanks).delete shift:=xlShiftUp
End With
End Sub

 

[Marcol]

To all, and myself especially...
Might be an idea to add to your code a check, that determines whether there are filters applied to the sheet or not.

And there might be other things to check, but where do you draw the line between the sublime and the ridiculous?

To again quote Biff ... KISS

Happy to have helped ...

 

[koolgeex]

Man, so everything was great and wonderful but i just had to make some edits to my sheet that added more columns. I thought all I would have to do is change the column letter in the code but when i changed it from "K" to "P" it no longer works. Now all it does it delete the top row (my header) and move it to my "Closed Jobs" sheet. It does'nt even touch the rows with "Closed" in the "P" column.

Here's the old code that worked fine:

Sub MoveToClosed()
    Dim LastRow As Long, NextRow As Long
    Dim ws1 As Worksheet, ws2 As Worksheet
    
    Set ws1 = Sheets("Complete Backlog")
    Set ws2 = Sheets("Closed Jobs")
    NextRow = ws2.Cells(Rows.Count, 1).End(xlUp).Row + 1
    
    With ws1
        LastRow = .Range("A" & Rows.Count).End(xlUp).Row
        .Columns("K:K").AutoFilter Field:=1, Criteria1:="Closed"
        .Rows("2:" & LastRow).Copy ws2.Cells(NextRow, 1)
        .Rows("2:" & LastRow).EntireRow.delete
        .Columns("K:K").AutoFilter
    End With
    
    Application.CutCopyMode = False
    
End Sub

...and then here's the code where i changed the column letter.

Sub MoveToClosed()
    Dim LastRow As Long, NextRow As Long
    Dim ws1 As Worksheet, ws2 As Worksheet
    
    Set ws1 = Sheets("Complete Backlog")
    Set ws2 = Sheets("Closed Jobs")
    NextRow = ws2.Cells(Rows.Count, 1).End(xlUp).Row + 1
    
    With ws1
        LastRow = .Range("A" & Rows.Count).End(xlUp).Row
        .Columns("P:P").AutoFilter Field:=1, Criteria1:="Closed"
        .Rows("2:" & LastRow).Copy ws2.Cells(NextRow, 1)
        .Rows("2:" & LastRow).EntireRow.delete
        .Columns("P:P").AutoFilter
    End With
    
    Application.CutCopyMode = False
    
End Sub

Any ideas why it won't work anymore?

 

[Marcol]

The method Autofilter, when called from VBa can fail if you have no headers, so in this case P1 would best not be blank.

Is Column A the correct column to calculate LastRow?

 

[koolgeex]

P1 has my header - It extends from A1 to Q1. Right now - column A is all empty ( i just added a new column ). Could that be the problem?

 

[koolgeex]

anything?

 

[Marcol]

Right now - column A is all empty ... Could that be the problem?

Yes!!!

Change this line to refer to the column that has the most data in it.

LastRow = .Range("A" & Rows.Count).End(xlUp).Row