Extract data from one sheet to another based on company name

[Moonpie]

So I'd like a macro perhaps, that will search a list of companies and extract the whole row of that company listed into another sheet -- for all instances of that company. Say I have a list that looks like this:

Company	Location	Revenue
Hostess	TX	$100
PetSmart	AR	$250
Yahoo	IN	$200
Vagisil	MN	$900
Yahoo	IN	$200
Sprite	FL	$400
Yahoo	IN	$200

I want to pull all 3 instances of Yahoo, and the other rows associated with it, into another sheet.

The number of companies will vary each month, listed hundreds of times or maybe only a few times. I don't want to have to sort manually, just want to push a button and have them in my sheet. Any ideas?

Thanks

[tonywig]

Have you considered using filtering or are you really wanting a macro ?

[Moonpie]

A macro. Also, there may be different naming conventions like: Yahoo Co. or Yahoo Company...stuff like that.

[Max, Singapore]

Assume your source data as posted is in Sheet1, cols A to C, data in row 2 down

In Sheet2,
Assume the company name input (core name) will be made into A2, eg: yahoo
Put in B2:
=IF($A$2="","",IF(ISNUMBER(SEARCH($A$2,Sheet1!A2)),ROW(),""))
Put in C2:
=IF(ROWS($1:1)>COUNT($B:$B),"",INDEX(Sheet1!A:A,SMALL($B:$B,ROWS($1:1))))
Copy C2 to E2. Select B2:E2, fill down to cover the max expected extent of source data, eg down to row 200? Minimize col B. Cols C to E will return the expected results for the name input in A2, all lines neatly bunched at the top