Macro to search all sheets and return corresponding rows of searched value

HI all,
I have a workbook with 13 sheets. Each sheet has 20,000+ rows. The format is the same on all sheets and columns A & B are unique (Item number & Item name). Currently, if someone needs to find information about an item, they have to use the find function, and go through the sheets. As you can imagine, this is time consuming and tedious.

What I would like to do is:
1. By the use of a userform or by any other method, type in the data you want to search.
2. A macro will then loop through the sheets, find the corresponding rows, on all sheets, and paste it in another sheet. There will also be duplicates on all the sheets and they need to be included in the returned search.

I've been looking online, and there are codes that compare or search through 2 sheets. Unfortunately, I do not have the expertise to modify these codes to suit them to my needs. I was hoping that someone can help me out or even point me in the right direction.

Thanks

Hi,
To understand the structure of your file, can you post a sample file with 2-3 tabs only and some rows (10 is enough).

Hi GC Excel,
I've attached a sample. In the original file, all the columns have data, but in this example I didn't include all of them as they always keep changing.
The 'Search' sheet shows the expected result. Sheet '1','2' and '3' contain the data. Fyi, cell colors do not matter.


Thank you.

Hi amar05,
See attached file.
Search criterias go in the yellow cells. You can use wildcard characters such as * and ?
Macro is in Module1 :

Please Login or Register  to view this content.

Another one

Hi GC Excel & AB33,
Both codes worked like a charm.

Thanks for your help.

Tried running the macro.Really a great solution.
For every search the result is getting cotinued from the previous search.Is it possible to clear the record for the next search such that it does not keeps on adding to so many rows.
Pl help.

Thanks
Sanyaa