If cell = closed project, cut cell and past into “Projects closed” worksheet on next available row

Question

Currently working on a spread sheet to track work progress. row "C" has a drop down from cells C12:C3000, if this is selected to "Project closed" I need to row to be cut and paste to the worksheet "Project closed" with no empty row left once the row is cut.

I have come across this VBA code and adapted it to my own use. but to no avail. I am very new to the world of VBA so not sure I've done it right.

Sub cut_paste() Dim i As Variant Dim endrow As Integer Dim ASR As Worksheet, LS As Worksheet

Set ASR = ActiveWorkbook.Sheets("In Progress")
Set LS = ActiveWorkbook.Sheets("Project Closed")

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

For i = 3 To endrow
    If ASR.Cells(i, "C").Value = "Project Closed" Then
       ASR.Cells(i, "C").EntireRow.cut Destination:=LS.Range("A" & LS.Rows.Count).End(xlUp).Offset(1)
    End If
Next

End Sub

BruceWayne BruceWayne · Accepted Answer · 2017-06-28T14:20:57

If you're going to cut rows, and don't want an empty row left, try this instead:

For i = endrow To 3 Step -1
    If ASR.Cells(i, "C").Value = "Project Closed" Then
           ASR.Cells(i, "C").EntireRow.Copy Destination:=LS.Range("A" & LS.Rows.Count).End(xlUp).Offset(1)
           ASR.Cells(i, "C").EntireRow.Delete
    End If
Next

This should go through the ASR sheet, copy any matches, then remove that row and continue onwards.

If cell = closed project, cut cell and past into “Projects closed” worksheet on next available row

1 Answers