Search multiple worksheets for Text String, copy and paste entire row into new worksheet

I need to search multiple worksheets for a text string (Customer Name) I enter and then copy and paste the entire row of all matches into a new worksheet (ideally with the Customer Name as the worksheet title) for further analysis.

I have 18 worksheets in a workbook and the customer names are located in Column B of all the sheets. I have found threads with some similarities to my question but I don't know enough about VBA to understand how to edit the code to get what I need.

I am using Excel 2010 and am attaching a sample workbook. Try customer names Sam Spade, Mary Jones or Fred Smith to test.

Thanks for your help,

Steve

Hi

I notice that in your example file the 2007 sheet has a different set of headings from the other sheets. How do you expect to reconcile for the different column headings / positions?

rylo

I have data from two different sources and yes the headings don't match; but I have ensured that the information I need appears in the same columns.

Hi

So you only want to bring across the data from columns A-B, G-O to the consolidated sheet?

rylo

Actually, after I looked back at my data, I realized it would be easy to reconcile by adding the missing columns to each sheet so all data will match. I've done that now.

This looks great but when I copy the module to my full worksheet and run the macro I get: Run-time error '9': Subscript out of range

Oh! That's the problem! I'll try that now.

Thank you so much,

Thanks to oeldere for the solution to my problem. The code worked once I added the Consolidated sheet and I'm learning to use the Pivot Table to view the data in different ways. Great solution!

Thanks so much for your help.