searching multiple worksheets and collecting the results

[ibis10]

I have a large amount of data across multiple worksheets, the worksheets are not uniform in column or row number or column names (the data is from a variety of different sources!). I'm trying to create a macro to produce a search box and search all the worksheets for keywords that might appear in any cell and when found to then copy that entire row into a new sheet "Results". The search string is preferably for multiple words so that the search only needs to take place once. The aim of the macro is to provide a way for different offices to search for information that only they need so for example:

Office A - searches for cats
Office B - searches for cats & kittens
Office C - searches for dogs

The different offices can't be relied on to filter or individually search all the worksheets and I don't know exactly what each office is searching for to produce the results for them so this seemed the best way to search in a comprehensive way and to produce the data that they can then use themselves.

I've been playing with this for a while and searching the site for tips and suggestions but haven't been able to create anything that functions in the way I need it to across multiple sheets. I can either get the search-box working or a single search-word function or the results sheet - not all of them working at the same time. Any suggestions on how to do this would be really appreciated...

[kcarley]

Ideally you might have sample data, nothing confidential, that we could use to work with? This seems like a simple enough logic that I may be able to help without it though...

Regards,
K

[ibis10]

Sorry - of course! This is a really short outline of the kind of spreadsheet I'm using, in real life there are thousands of lines of data.

excel test.xlsx

Essentially it is a collection of file references and more detailed account descriptions, we're trying to work out who needs to have access to what after the departure of a long-term member of staff.

[kcarley]

Sorry for the delay; trying to pay the bills too.

Is all the data in the same Workbook?

[AB33]

Try this one

Please Login or Register  to view this content.

[kcarley]

Hi!

Did AB33's solution work for you? Are all the worksheets in the same workbook?

Since it has been a while since you've posted I'd like to know if you still need a solution as you've described. AB33's code failed for me but if it's working for you then that's great! Please reply with the needed answer here for me to continue.

Cheers!
K

[AB33]

K,
What is not working for you? It does work for me on the attached sample .

[ibis10]

Thanks guys - sorry to take so long to reply, I've been on holiday. This did work for me, thank you so much to you both for your help!

[AB33]

Could you please now close this thread as solved. Go in to the top right-hand side of this page, choose "Thread Tools" you then select solved from the drop down menu?

[kcarley]

My absence was due to illness but I did have this almost together just before.

#AB33
In response to:

K,
What is not working for you? It does work for me on the attached sample .

1. I tend to work with "Option Explicit" so I first received a compile error on the variable "ms".
2. Then again on variable "i".
3. I received a Subscript out of range because I didn't first add the "Results" sheet.

Then it worked great! ... but the only thing is that the last search item didn't populate with the row data, only the "Sheet" that you put in column "A".

I also though the OP requested

a macro to produce a search box

which yours did not.

I did have an alternative to handle multiple workbooks but since ibis10 likes your solution I pulled finishing that and came up with this.



#ibis10

You can add this to your personal.xlsm if you like to provide an always available macro; I've only added a Workbook_Open event to help in this example. Let me know if you have any troubles.

Cheers!
K