filter for different sets of colored data

If you guys don't mind, can you help me with another problem?
In the attachment, each color is one set of data, how can I get to sheet 2 (not manually)? I try to use filter, but it doesn't do the job.

Thank you so much!

I'm sure there's a few ways.

I'm partial to Advanced Filter Macros because they're easy to deconstruct and explore VB. It's how I got my start.

If the button doesn't work, you can call Macros with Alt+F8 and choose the one you want.


Here's the code for it:

Sub RainbowUnicorns()

Sheets("Sheet2").Range("A1:I100").Clear

Sheets("Sheet1").Range("A1:C100").AdvancedFilter _
        Action:=xlFilterCopy, _
        CriteriaRange:=Sheets("Filter").Range("A1:C2"), _
        CopyToRange:=Sheets("Sheet2").Range("A1"), _
        Unique:=False

Sheets("Sheet1").Range("D1:F100").AdvancedFilter _
        Action:=xlFilterCopy, _
        CriteriaRange:=Sheets("Filter").Range("D1:F2"), _
        CopyToRange:=Sheets("Sheet2").Range("D1"), _
        Unique:=False
        
Sheets("Sheet1").Range("G1:I100").AdvancedFilter _
        Action:=xlFilterCopy, _
        CriteriaRange:=Sheets("Filter").Range("G1:I2"), _
        CopyToRange:=Sheets("Sheet2").Range("G1"), _
        Unique:=False


End Sub

Quick walkthrough:
The first line kills the results page so you start fresh each time

Next are 3 blocks of code, each very similar. They are sorting each chunk of data by comparing it to the filters on the filter tab.

So, A1:C100 on sheet1 is being compared to A1:C2 on the Filter tab. Things that match the fields with the headers are copied over.
This is then repeated for the other data sets. You just need to make sure the data sets have headers (which I added in my example).

Here's another way using formulas and helper columns.

I used 3 helper columns on sheet1 J:L, and copied this down in each column...
J2=IF(A2="008",J1+1,J1)
K2=IF(D2="008",K1+1,K1)
L2=IF(G2="008",L1+1,L1)

Then on sheet2A1, copied down and across to C2...
=IFERROR(INDEX(Sheet1!A:A,MATCH(ROW(A1),Sheet1!$J:$J,0)),"")

Repeat this for the next 2 sets of 3 columns, changing the last reference for each "set of 3"
=IFERROR(INDEX(Sheet1!A:A,MATCH(ROW(A1),Sheet1!$k:$k,0)),"")
=IFERROR(INDEX(Sheet1!A:A,MATCH(ROW(A1),Sheet1!$l:$l,0)),"")

you'r welcome. If this answered your question, please take a moment to mark the thread as "solved" - it helps keep things neat and tidy lol, and consider adding the reputation to those that helped (see points 2 & 3 below my 1st post to you)