Search text in worksheets + copy to new sheet

Hey,

I've tried searching for an answer to my query but nothing quite works / fits - and I'm not really familiar enough with VBA to edit it enough to make it work.

I have a workbook of many worksheets. Worksheet 1 has a list of names, with a header in row 1 "List", and below it are about 70 rows of alphanumeric one word text. I need to search all the other worksheets (worksheet 2 to the last worksheet) to see if any of those names in worksheet 1 appear. All the worksheets are formatted: column a + b + c + d contain data, row 1 is a header. In worksheet 2 onwards the alphanumeric one word text would appear in a text string in a cell. If they appear, I need to copy the entire row it was found in AND the title of the worksheet it was found in to a new worksheet.

Attached is a template worksheet.

Thanks!!

Hello stonegwene,

Are you searching for whole word matches or partial matches as well?

Hello ,

If your example is true to the original data then a whole word match will not work with the example below:

Name_Symbol=AAAA random info after

The reason is a whole word match expects the word to have a space before and after the word unless it is the first or last word in the string.

Hello stonegwene,

Using a partial match, I can set the macro to add an equal (=) in front of the AAAA and a space after it. But this would work only if the format were always =AAAA .

Hello stonegwene,

Okay, I can do that for you.

Hello stonegwene,

This macro has been attached to a button on "Sheet1". The attached workbook has the macro and button added.

Please Login or Register  to view this content.