Find and Select, Copy and Paste.

**johnlovesbeer** · 06-26-2011, 10:44 PM

Thanks in advance for any help!
If I do a search within an entire workbook and get hits from multiple worksheets is there any way that I can can copy all the results in one batch and paste to a new worksheet?
Cheers.

**MarvinP** · 06-26-2011, 11:18 PM

Hi john,

I was thinking you would have to do a bunch of FindNext after a Find command but then I found this. See if it helps
http://www.cpearson.com/excel/FindAll.aspx

**johnlovesbeer** · 06-26-2011, 11:59 PM

Thanks for the reply Marvin but I don't think that is exactly what I'm looking for. I've attached a screenshot of the workbook and search. What I want to be able to do is select and copy listings from the search results box and then paste them into another worksheet. I need to do every listing in one hit but unfortunately I can't select listings from more than one worksheet at a time. I'm not much good with code so the link you forwarded was fairly confusing to me.

**johnlovesbeer** · 07-06-2011, 07:21 PM

Still struggling with this!

**MarvinP** · 07-06-2011, 08:04 PM

Hi John,

It seems the only way to get this Find across multiple worksheets to produce a list on a single worksheet is by using VBA. I see your picture of Find All and you really want this list to be in a workbook not in the dialog.

I've just looked a second time for some non VBA method and don't find it.

I've seen this question before on this site. I'm thinking you need to search a little harder to find a very similar problem. If now then we need a sample workbook with which to write some code.

**johnlovesbeer** · 07-06-2011, 09:23 PM

Thanks again for the reply Marvin. I've attached a sample of the workbook. You will notice a few worksheets with customer names that have no entries. This is related to a question I posted in the Excel Programing forum to which I had no success- http://www.excelforum.com/excel-prog...ml#post2547935
After getting no solutions to that post I went with the idea in this post of simply copying search results and pasting them into the relevant worksheets but as you know this isn't working for me either. I know that forum rules forbid me linking threads together, I just wanted to explain a little better as to why I need to find a way to do this.

Cheers, JLB.

**MarvinP** · 07-06-2011, 09:56 PM

Hi John,

I have an idea. See the attached. I've been telling people Excel works best if all the data is in a big table instead of separate sheets or workbooks.

I copied your 3 data sheets to a single one. I then did an AutoFilter on the column A. Then used the Text Filter using CONTAINS what you want. Copy and paste what is left to a new sheet. Better yet is leave them in a single table and don't need separate sheets.

**johnlovesbeer** · 07-06-2011, 10:04 PM

Pretty good idea, and I will probably end up going that way if we can't come up with anything else. The down side is that it will be a massive table as there will be about 4000 entries.
If you do think of anything else that would be great, otherwise thankyou very very much for your help!

JLB.