Excel macro to cut a specific row of cells from one worksheet to another

Question

I have a worksheet titled CASES-PENDING, with many rows of data. On a daily basis I change the status of a particular row's beginning cell to "done" (changed from "pending"). Instead of then having to cut that row and paste on my other CASES-DONE titled worksheet, I'd like a macro to do that. I want to run the macro after changing the status of several rows of data, from "pending" to "done". Then all those rows must be cut and pasted on the other worksheet. Is that possible? Thanks so much guys!

Answer 1

This is just a stab in the dark, but I've been handling something similar to this as of late.

Dim LastRow As Long
Range("1:1").AutoFilter Field:=(Row you have "Done" in), Criteria1:="Done"
LastRow = Cells(Rows.Count, 3).End(xlUp).Row
Range("CellRangeYouNeedCopied" & LastRow).Copy Destination:=Sheets("SheetX").Range(X,Y)

Basically this filters to only the rows with DONE in them, and copys and pastes them on whatever other sheet you decide to name. Just remember to replace all of the variables. I'm still pretty new at this, so I may be wrong but it's worth a shot!

Edit:You can also just record this as a macro, and then change the range so that it's variable using the Long variable.

Answer 2

You can also do it like this. More code, than the AutoFilter solution, but maybe more flexible.

Sub MoveDoneRows()
  Dim nStatusCol As Integer
  nStatusCol = 1

  Dim i As Integer
  i = 2

  ' select first row to insert rows into DONE sheet
  Dim nInsertRow As Integer
  Sheets("CASES-DONE").Select
  Range("A1").Select
  Selection.End(xlDown).Select
  nInsertRow = ActiveCell.Row + 1

  ' move rows with status done
  Dim sStatus As String
  Dim sPasteRow As String
  sStatus = Sheets("CASES-PENDING").Cells(i, nStatusCol).Value
  While sStatus <> ""
    If sStatus = "done" Then
      ' cut the current row from PENDING sheet
      sPasteRow = i & ":" & i
      Sheets("CASES-PENDING").Select
      Rows(sPasteRow).Select
      Selection.Cut

      ' paste into DONE sheet
      Sheets("CASES-DONE").Select
      Cells(nInsertRow, nStatusCol).Select
      ActiveSheet.Paste
      nInsertRow = nInsertRow + 1

      ' delete empty row from PENDING sheet
      Sheets("CASES-PENDING").Select
      Rows(sPasteRow).Select
      Selection.Delete Shift:=xlUp
    Else
      i = i + 1
    End If
    sStatus = Sheets("CASES-PENDING").Cells(i, nStatusCol)
  Wend
End Sub