Move Rows from multiple sheets in workbook to single sheet based on cell value

[ryankendrick]

I am wanting to use a Macro to move/copy entire rows from multiple sheets in a workbook to a single sheet when a cell is marked as "COMPLETED" or "CANCELED". I only want to copy then hide the rows, NOT delete them. I have 6 sheets that all have information that needs to be moved to a 7th sheet for the completed items. The "trigger" is manually entered into the cell in Column "I" when the task is completed. I would like for the moved information to just "stack" on the last sheet. I don't know if that makes sense, but as a new item is completed, it just adds it to the end of the list on the 7th sheet. Thank you.

 

[Jamie McMillan]

Hello Ryan,

I assume that the macro will be called if the data in cells (i, x) is changed. If so the following code will work through the Sheet 4. You can just update it to sheets 1, 1, 3, 5 & 6. If you need additional help just ask.

Sub CopyAndHide()
Dim i As Integer

    For i = 1 To 16
    If Cells(i, 9).Value = "COMPLETED" Then
    Cells(i, 9).Select
    Rows(ActiveCell.Row).Copy
    Sheets("Sheet7").Select
    Range("A1").Select
    Selection.End(xlDown).Offset(1, 0).Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    Sheets("Sheet4").Select
    Cells(i, 9).Select
    Rows(ActiveCell.Row).EntireRow.Hidden = True
    End If
    If Cells(i, 9).Value = "CANCELED" Then
    Cells(i, 9).Select
    Rows(ActiveCell.Row).Copy
    Sheets("Sheet7").Select
    Range("A1").Select
    Selection.End(xlDown).Offset(1, 0).Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    Sheets("Sheet4").Select
    Cells(i, 9).Select
    Rows(ActiveCell.Row).EntireRow.Hidden = True
    End If
    Next i
    Range("A1").Select
End Sub

Jamie