Help expanding the search criteria on an existing macro

[DaveBre]

Hi all,

I'm looking for some assistance in modifying a macro to expand what it's searching for/returning.

I have data in about 150 worksheets within a workbook, within each worksheet there are certain keywords that I'm searching for (Employee ID being the first, with cost centre and location also being required.

The following code will search through all sheets looking for Employee ID, provided some text is in at least column A it will copy the row across to a separate sheet (summary tab) along with the sheet name being dropped into column O.

Please Login or Register  to view this content.

I'm looking for help in doing two things:

1. Expanding the search criteria to include more than one text string (3 or 4 would be ideal)
2. Understanding what happens when the data doesn't start in column A (as the results are intermittent).

I've attached sample workbook showing how it works.Searching for Text.xlsm

Any help would be appreciated.

Cheers

David

[AB33]

I have amended the code on module 2.

[DaveBre]

Hi AB33,

Thanks very much for modifying the code, I have a copule of questions:

For some reason if I put "location" in F10 and number in F11 of Sheet 5 this isn't picked up and returned by the macro (any idea why?), also the summary sheet appear to have duplicates of the data from sheets 2,3,4, but sheet 5 is only displayed once.

Any idea why?

Thanks

David

[AB33]

Hmm?
Use this line

Please Login or Register  to view this content.

Instead of

Please Login or Register  to view this content.

[DaveBre]

Hi AB33,

Thanks - that's fixed the duplicates in the summary tab.

Any idea how I can return one of the text strings if it starts in Column B or to the left of Column A?

Thanks

Dave

[AB33]

The code is searching in all columns. If you want to search in column A ONLY

With ws.columns("A")