Finding and copying multiple criteria using macro

Hello friends. I’m new member of this forum and I hope that you could help me.
Before describing my issue, I want to admit that I’m not very good at VBA and writing macros, so I do hope that criticism from more experienced member will be avoided. I have done search through internet and could not find macro that would be appropriate for my case.

I’m working with huge file, like 50k-90k records in each sheet, and sometimes sheet number goes up to 10 or even more. My job is to find if these record contain names of certain products: Product1, Product2,…….etc. The min. amount of search terms as a rule is 10 (from Product1 to Product10), but sometimes it goes up to 70.

I’m interesting if it is possible to write a macro that will help me to define if cells in all sheets contains product names(only), if contains copy this name in new workbook. The name of products remain unchanged, but there is a problem with cell value, as sometimes they contain product name in following order: Product1(010170), or like # 22847 Product2, or # Product3 87622. There have been cases where I found these name in texts: There has been 1% decline in sales of Produc1 etc.

Please help me resolve this problem.

Thank you very much

Originally Posted by davesexcel

To best describe or illustrate your problem you would be better off attaching a dummy workbook, the workbook should contain the same structure and some dummy data of the same type as the type you have in your real workbook - so, if a cell contains numbers & letters in this format abc-123 then that should be reflected in the dummy workbook.

If needed supply a before and after sheet in the workbook so the person helping you can see what you are trying to achieve.

Doing this will ensure you get the result you need!

Tanks and

Hope this file will help you to understand general idea of my issue,

No one knows how to resolve this issue?

Kotler,

I am looking into creating a solution for this. I'll be able to get something to you on Monday (3/28). Hopefully you are still looking for something.

I have to warn you though, searching through that much data can cause stack problems, and it can take a very long time for a search to complete.

Wow thanks friend. Actually I know that, search will take some time, but I'm sure that it want take much longer then manual search using ctrl+f.

I'll be looking forward for your reply.

Kotler,

I have attached a modified version of your dummy sheet that contains a working macro to perform the search and put the results into a new workbook. Let me know if you have questions.

~tigeravatar