An overview of what is required is ...
Loop thru the twenty sheets. (ActiveWindow.SelectedSheets)
On each sheet, use the Find function ( in another loop ) to search for the text.
When an item is found, identify the row number and sheet name and list it
on another sheet ...

It took about 700 lines of code for me to accomplish the above, except the
entire row containing the found stuff is show on a separate sheet.
Also, one can enter multiple items to search for.

You can download the free trial of XL Companion from my website and give
the "Find and List" utility a try to see how successful? it is.
Maybe it will do what you want.
Comments and suggestions about the program are welcomed...
http://www.realezsites.com/bus/primitivesoftware
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)




"Zak"
wrote in message
Can you not offer me any guidance as to how to write it? i mean i know what
to do but dont really know how to get around it..

thanks in advance.

"Jim Rech" wrote:

No I don't have such a macro. It would probably be highly specific to your
exact situation so you would have to write it yourself or get someone you
work with to do it.

--
Jim