Excel - Copy row based on a coloured cell

[alip43]

Hi
I have a spreadsheet (in office 7) which contains columns for:- names of students, yr, tutor group, various units.

The units are coloured green, amber or red.

I need to create a macro button that will look at the information on sheet 1 and copy the row onto sheet 3 if it meets the following criteria - a cell in this row is amber or red.

I want to end up with a sheet that has the same headings as sheet 1 but with only the students who are falling behind with their course work.

Hope someone can help
Thank you

 

[lionelhill]

Have you tried "Filter" on the data ribbon?

 

[kjv1611]

Another thing you could try, I think, is using MS Query to query the data you want into the 2nd worksheet... if you can use that within the same Workbook - I would think you can, but I don't recall ever trying myself... that is if you have to have the data in the other worksheet specifically..

Also, on the Filter option mentioned by lionelhill, it wouldn't be pretty, but if you're in a pinch, and can't get it sorted out another way, you could copy the 1st worksheet, and on the copy place your filter. That way, if you need all the data to still show on the first sheet, but you also need a separate filtered sheet, you can have both. I'm NOT suggesting this as an all the time, normal/regular fix, but rather "I'm in a pickle, and gotta get this fixed NOW type situation."

--

"If to err is human, then I must be some kind of human!" -Me

 

[AnotherHiggins]

lionelhill: I've had Excel 2007 for many, many months now and never realized that they added "Filter by Color". Nifty.

But still.... Surely there's logic behind why the cells are colored as they are, right, alip43? You indicate that Red/Amber = "students who are falling behind with their course work". So can you tell from the data (ignoring the color) on the spreadsheet whether a student is falling behind?

I'm thinking of a solution like KJV suggested. I've built queries like this many times. The nice thing is that once it's set up you can just go to the Data Ribbon and click on Refresh All, which will update the data on each of the sheets based on the newest data on your master sheet.

[tt][blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[lionelhill]

yes, even were I using the filter, I would filter by the underlying thing that determines the colour, rather than the colour itself, because I think "filter by colour" looks only for the colour you want in the one column where you choose it. If you have a situation where a red box may appear in any of several columns, and you want to find all rows with a red box somewhere, it would be messy (at least without a helper column itself coloured red)