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

 

[Rick Rothstein]

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

Still getting the same error but I did notice it deleted 10 from the main sheet and pasted 7 in the "closed jobs" sheet. Still left 21 marked "close" on the backlog sheet. Man!

The word "closed" and the word "close" are not the same, so you have to look for both of them individually in your test, not just one of them. Try this in your originally posted code (replacing the part I showed in red above)...

If LCase(.Range("K" & i).Value) = "closed" Or LCase(.Range("K" & i).Value) = "close" Then....

 

[koolgeex]

Opps sorry that was a typo. All cells contain "Closed".

 

[koolgeex]

..and user select the value from a dropdown in column K. They are all they same.

 

[JoeMo]

Opps sorry that was a typo. All cells contain "Closed".

Both versions I posted are working fine for me. If all the rows marked "Closed" are being removed from the backlog sheet, but some of them do not appear in the Closed Jobs sheet then the issue is not connected to the If statement. Do you have any hidden rows in the Closed Jobs sheet?

 

[Rick Rothstein]

..and user select the value from a dropdown in column K. They are all they same.

Okay, try running your loop backwards then...

For i = LR To 1 Step -1

 

[koolgeex]

I tried doing it backwards too. Not working for me. I've uploaded it to my web host so you can download and see. Please Please Please take a look. I just don't know what i'm missing. click to download my workbook

 

[JoeMo]

Okay, try running your loop backwards then...

For i = LR To 1 Step -1

That shouldn't make any difference since the delete and shift is done after all data is transferred. I suspect that hidden rows on the receiver sheet may be the problem.

 

[JoeMo]

I tried doing it backwards too. Not working for me. I've uploaded it to my web host so you can download and see. Please Please Please take a look. I just don't know what i'm missing. click to download my workbook

Do you have any hidden rows in the receiving area of the completed jobs sheet?

 

[koolgeex]

There's no hidden rows. Just a header row and the rest is blank.

 

[koolgeex]

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]