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 = .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("A1:K" & LR).SpecialCells(xlCellTypeBlanks).Delete shift:=xlShiftUp
End With
End Sub

 

[koolgeex]

Nope. This time it didn't paste row 21, 25, or 30 - But it's still deleted from the Backlog. Could it be something wrong with the "Closed Jobs" spreadsheet?

 

[Ingolf]

koolgeex,

This post is similar with Macro for Button: If Cell = Name then Copy to Another Spreadsheet (frome same forum) and I think you must try in another sheet, cause that sheet in this case "Closed Jobs" and in that post sheet "Complete Backlog",is something wrong,

 

[koolgeex]

I will try taking all of my dropdowns and fancy things out to see what I can do --- but i need those things added back in for the final product so I'm not sure how much good it will do.

 

[Ingolf]

Don't try to remove, just try in another sheet

 

[koolgeex]

I created a new sheet called "Sheet1" and I changed the macro to match. It still does the same thing, copies the correct data from "Complete Backlog" but then it pastes it starting at Row 38 instead of Row 2.