Macro to Cut Closed items (rows) from One Sheet and Paste into Another

[koolgeex]

I'm using 2003 (company computer ).

I need the macros to manipulate cells not entire rows So here's the algorithm: If a cell in Column K of the "Complete Backlog" spreadhseet is equal to "Closed" then cut cells A?:K? of the same row and paste into the next available row of the spreadsheet named "Closed Jobs".

[Single example: On "Complete Backlog" spreadhseet, if K2 = "Closed", cut A2:K2 and paste into A?:K? of "Closed Jobs" speadsheet in the same workbook.]

I have the following code, but it just completely deletes all of the content on one sheet for whatever reason. Not sure what I did or how I can fix it.

Sub refresh()
Dim MY_ROWS As Long
With Sheets("Complete Backlog")
For MY_ROWS = 1 To .Range("A" & Rows.Count).End(xlUp).Row
If .Range("M" & MY_ROWS).Value = "Close" Then
.Rows(MY_ROWS).Cut Destination:=Sheets("Closed Jobs").Range("A" & Rows.Count).End(xlUp).Offset(1)
End If
Next MY_ROWS
On Error Resume Next
.Columns("M").SpecialCells(xlCellTypeBlanks).EntireRow.delete
End With
Application.CutCopyMode = False
End Sub

 

[VoG]

Try

Sub refresh()
Dim LR As Long, i As Long
With Sheets("Complete Backlog")
    LR = .Range("A" & Rows.Count).End(xlUp).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("A1:A" & LR).SpecialCells(xlCellTypeBlanks).Resize(, 11).Delete shift:=xlShiftUp
End With
End Sub

 

[koolgeex]

Thanks for the quick response! I received an Application-Defined or Object-Defined error (404). Then it highlighted this line in yellow:

.Range("A1:A" & LR).SpecialCells(xlCellTypeBlanks).Resize(, 11).delete shift:=xlShiftUp

 

[VoG]

Try

Sub refresh()
Dim LR As Long, i As Long
With Sheets("Complete Backlog")
    LR = .Range("A" & Rows.Count).End(xlUp).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("A1:K" & LR).SpecialCells(xlCellTypeBlanks).Delete shift:=xlShiftUp
End With
End Sub

 

[koolgeex]

Hm, looks like it is working slightly but it's only copying and deleting the first two rows of items marked "Closed". I'm not sure what's happening to the other items. It looks like they are deleted. Now that I'm looking more closely it seems like the contents of each row are being moved around - as in cells not staying adjacent to what they were originally put next to.

Did that make sense? Let me know how I can explain it better.

 

[koolgeex]

Actually, it looks like it's working and it moves the correct cells. It doesn't seem to check values towards the end of my datasheet though. Something on Row 10 marked "Closed" is moved but something on Row 41 marked "Closed" is still there.

 

[VoG]

Try

Sub refresh()
Dim LR As Long, i As Long
With Sheets("Complete Backlog")
    LR = .Range("K" & Rows.Count).End(xlUp).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("A1:K" & LR).SpecialCells(xlCellTypeBlanks).Delete shift:=xlShiftUp
End With
End Sub

 

[koolgeex]

Man, something weird is going on. I've tested it 3 times and each time row 30 is never copied over when it's marked as "Closed". All the other rows seem to copy over except on my last time, row 26 was marked "Closed" and did not copy over. Row 30 and row 26 were both deleted from the "Complete Backlog" sheet though. Suggestions?

 

[koolgeex]

Anyone?

 

[koolgeex]

Are there any other solutions to this problem?