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]

Try this

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

Change "A" for ws1 if Column A doesn't contain your last row of data.

 

[JoeMo]

I also just copied the "Complete Backlog" sheet and changed the name to "Closed Jobs" since the headers are the same. I deleted the data and then ran the macro. Still didn't work. I'm using Excel 2003 if that makes a difference. again here's the workbook [here]

Sorry, I'm reluctant to download a workbook that's doing strange things. Have you tried closing Excel, open a new workbook and creating two sheets ("Complete Backlog" and "Closed Jobs") with mock data, then copy/paste the code I posted and run it?

 

[koolgeex]

Not sure I know what you mean by "main indexing". Either way, I ran the code and It deleted everything from my "Complete Backlog" worksheet. When i view the "Closed Jobs" sheet it's doing something funky. It pasted all of the rows from the "Complete Backlog" but it hide them when it pasted. I had to "Unhide" to see that it actually pasted data.

 

[koolgeex]

JoeMo,

I created a new workbook. New spreadsheets. I'm still gettin an error and it's still deleting a few and pasting a few. Could you upload your version somewhere or email it to me?

 

[Marcol]

... Either way, I ran the code and It deleted everything from my "Complete Backlog" worksheet. When i view the "Closed Jobs" sheet it's doing something funky. It pasted all of the rows from the "Complete Backlog" but it hide them when it pasted. I had to "Unhide" to see that it actually pasted data.

Just saw your file
Turn the Auto-Filter off before you run the code in Post #25

No looping required, the code applies the filter, copies the result to the destination file, then deletes the "Closed" rows and removes the filter.

Not sure I know what you mean by "main indexing".

You might have missed my edit to post #25

 

[JoeMo]

JoeMo,

I created a new workbook. New spreadsheets. I'm still gettin an error and it's still deleting a few and pasting a few. Could you upload your version somewhere or email it to me?

If you PM me with an email address I'll send it to you.

 

[koolgeex]

@Marcol --- DING DING DINGGGGGG!!!!!!!!!!!!!!

Turning off the autofilters in the headers did the trick.

Thanks so much to everyone! I've been struggling with this for about a month now!


For the record (in case someone comes to view this) - Use the code in reply #21 from Marcol. Make sure all autofilters are off before you run it!

 

[JoeMo]

@Marcol --- DING DING DINGGGGGG!!!!!!!!!!!!!!

Turning off the autofilters in the headers did the trick.

Thanks so much to everyone! I've been struggling with this for about a month now!


For the record (in case someone comes to view this) - Use the code in reply #21 from Marcol. Make sure all autofilters are off before you run it!

And ... for the record, does this mean you had filters turned on when you ran the original code you posted????

 

[TheCman81]

This is a looping bit of code I used before but no doubt someone will come back with something quicker

Sub MoveLines()
Application.ScreenUpdating = False
Dim Dsh As String
Dim Rend As Long
Dim Dd As Long
Dim Nend As Long
Dsh = "Complete Backlog"
Sheets(Dsh).Select
Rend = Cells(65536, 1).End(xlUp).Row
For Dd = 1 To Rend
    Select Case Cells(Dd, 11)
            Case "Closed"
            Nend = Sheets("Closed Jobs").Cells(65536, 1).End(xlUp).Row + 1
            Rows(Dd).Cut Destination:=Sheets("Closed Jobs").Cells(Nend, 1)
    End Select
Next Dd
Range("A2:K" & Dd).SpecialCells(xlCellTypeBlanks).Delete shift:=xlShiftUp
Application.ScreenUpdating = True
End Sub

 

[koolgeex]

@Joe - No I hadn't actually sorted the sheet using the autofilters but I had them set up so the users could sort if they needed to.