Sub CopyPasteColorHighlighted()
Dim LR As Long, i As Long
Sheets("Red, waiting on Stamp").Columns("A:O").Clear
Sheets("Green, Stamped needs invoice").Columns("A:O").Clear
Sheets("Blue, invoiced needs payment").Columns("A:O").Clear
Sheets("Cream, paid").Columns("A:O").Clear
With Worksheets("Main")
LR = .Cells(Rows.Count, 1).End(xlUp).Row '//Get last row #
For i = 13 To LR Step 2 'two rows per record except for the grey rows
Select Case .Cells(i, 1).Interior.Color
Case vbRed 'Red
.Cells(i, 1).Resize(2, 15).Copy _
Destination:=Sheets("Red, waiting on Stamp").Cells(Rows.Count, "A").End(xlUp).Offset(1)
Case 6723891 'Green
.Cells(i, 1).Resize(2, 15).Copy _
Destination:=Sheets("Green, Stamped needs invoice").Cells(Rows.Count, "A").End(xlUp).Offset(1)
Case 13421619 'Blue
.Cells(i, 1).Resize(2, 15).Copy _
Destination:=Sheets("Blue, invoiced needs payment").Cells(Rows.Count, "A").End(xlUp).Offset(1)
Case 13434828 'Cream
.Cells(i, 1).Resize(2, 15).Copy _
Destination:=Sheets("Cream, paid").Cells(Rows.Count, "A").End(xlUp).Offset(1)
Case 8421504 'Grey: skip this ONE row
i = i - 1
End Select
Next i
End With
End Sub
Bookmarks