How to search for text across multiple worksheets and copy rows?

Actually this helped too just had to modify and do some research about the script. Though I am not a pro.

I am looking at doing something quite similar to what the initial question was asking. The macro provided works for what I am trying to do, with two exceptions:

First; instead of looking for "change order", I am looking for either "Low Outlier" or "High Outlier". These two will be the result of the following formula:

=IF(O21="","",IF(O21<VLOOKUP("Low Outlier Cost",A:B,2,FALSE),"Low Outlier",IF(O21>VLOOKUP("High Outlier Cost",A:B,2,FALSE),"High Outlier","")))

The macro does not seem to recognize the result of this formula. If I copy the result and paste it as a value, however, then the macro recognizes it and performs the appropriate functions. Do I have to copy and paste all the results of above formula as values for this to work or is there a way around it? I have almost 100 worksheets in the document so it would be quite a mess to have to go through the whole thing.

Second; when the macro copies the various fields to the summary page, it copies the forumlas and not values. Therefore the results show up as #REF instead of the values of the cells. How do I change the macro to only pasting values and not formulas?