Macro to search for a criteria and begin copying data into a new sheet based on criteria

Is it possible to have a macro search for a word down column A and when it finds that word begin copying data in the row it found the word but in a differnt column, for example column E?

For example:

A B C D E F
1
2
3 YES 3 7
4
5

So when it finds the word Yes it would copy cells E3 and F3 into a new sheet.

Please Help I've been trying to figure this out for weeks. Thanks!!!

The easiest way I see to do the search function is to record your macro using the cntrl+F function it looks like this:

Please Login or Register  to view this content.

Hope this helps.

Thanks! this is a good start. I am not sure how to make the code begin copying the data in column E however.

This should work... Pastes into sheet 2

Please Login or Register  to view this content.

Thanks again! however now I am getting a run time error saying "select method of range class failed".