Populate one sheet based on values in another sheet

I newish to this,

I have a list of 1000 stores names in one sheet of which approx 100 have an flag 'y' against them in column b.

I need a routine to go through the list of 1000 store names and populate another sheet with the store names where the flag is 'y',

I need to run the regularity as the list of 'flagged' stores frequently changes

Any help gratefully appreciated

hi, GBR2L, please check attachment, run code "test"

Thank you for all the information, most help i've found for excel in a long time. Had one question for you though, what would the formula look like if i wanted do this with 6 columns? i have my sheet poplating the information but i can't figure out how to write the formula to remove the blanks?


These are the six columns have about 1200 rows of information with plenty of blanks on each sheet that has been broken down into color

a b c d e f
CES 10 0 E4220 6/7 7.5 & 9 Black


Thanks

Best that you post a sample of your workbook explaining what you intend using as a flag.
What do you mean by "...has been broken down into color"

Sorry for the long response,

i attached my workbook with the actual data, i have the value populating into each spreadsheet i would just like to remove the blanks using the formula used above but cannot piece it together to make it remove the blanks

Thanks