Macro to copy rows based on background color of cells within a range of columns

First I want to thank everyone in the forums for taking the time to help out others!!

After quite exhaustive searching this macro is the closest to what I need, so I thought I would ask for a little help.

I ran this macro but haven't figured out how to get the final touches on it.
A little background. I have an inventory with multiple worksheets, but not every worksheet needs to have rows copied. I am collecting all missing items on it's own worksheet. Missing items are highlighted red using a dropdown box with conditional formatting in a "status" column.

So what I need:
I need to specify multiple worksheets to search (ex. names SN06210, SN06255, etc)
I need to specify which worksheet to paste the data (Discrepancies)


And just as a question about macros in general- Will it update as people make changes or do I need to manually run the macro every time?

Here's a sample file.
Attachment 279328

Thank you so much!!

Oh boy. Well now that I added the dropdown with conditional formatting the macro doesn't work anymore.

I think this might work, but I need it to say the equivalent of

WITH Application.Find
Clear
Interior.Characters = Missing

But obviously, that's not the way to write it. LOL

Please Login or Register  to view this content.