Copy entire row to another sheet based on cell color

Hello everyone,

I have a workbook with multiple sheets. I would like to set an array of those sheets where cells in column B are checked on cell color.
I would like to copy all rows which match the specified color, together with comments and checkboxes, to another specified sheet.

Thanks in advance.

Kind regards,

Marco

Try the following code. You may have to change the colorindex and sheet name to suit your needs. This code will copy to the first blank row in your destination sheet.

Sub Test()
    Dim bottomB As Integer
    bottomB = Range("B" & Rows.Count).End(xlUp).Row
    Dim x As Long
    For x = bottomB To 2 Step -1
        If Cells(x, 2).Interior.ColorIndex = 3 Then 'This is the colorindex for the color "red".  Change it to suit your situation.
            Rows(x).EntireRow.Copy Sheets("Sheet1").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)
        End If
    Next x
End Sub

Hello Mumps,

Thank you for your reply. The code seems to work fine. I would like to have it work on all my sheets at the same time though, if possible. Now I need to run the code on all my sheets in order for it to copy the rows to the destination sheet.

Also, would it be possible to define columns to copy from the rows that match the specified color instead of the entire row? I tried with the entire row like you provided, but I change my mind on that one.

Kind regards and thanks for the help,

Marco

I would like to copy columns B:F and I if possible. And yes, this will be the same for all worksheets.

What is the name of the destination sheet?