Search whole workbook for keywords, pull whole row containing word/label/paste elsewhere

[HSG4L]

I'm trying to see if I can use a macro to search for a list of keywords within a whole workbook and then copy the entire row that contains the keyword, label it with the name of the sheet it came out of, then paste it into a new workbook or worksheet (whichever is easier).

To help with explaining, the workbook I'm pulling from is a list of different shipping ports (a different port in each sheet with the port name as the sheet name) in Australia and the freight ships that are at port or expected within that week (report comes in weekly). I need to pull the rows that contain the products my company deals with, as well as the destinations it's going to or coming from. So the keywords list will be something like "Cement, Iron, Aluminum, South Africa, China, India". I would need the macro to pull the rows in the whole workbook that have any of the keyboards. Put them in a new sheet or workbook and automatically label which port (or which sheet) the row came from. The problem is that in each of the sheets individually, the columns are not always labeled the same way. It could be "Vessel Name, Cargo, Weight of Cargo, Original Port, Shipping Company" (Columns A,B,C,D respectively) and the next sheet/port could be "Cargo, Vessel Name, Time of Arrival". Some sheets might have columns that others don't.

So I guess I'm looking for a way that will tell VBA to just search in the whole workbook, with no reference to any columns or rows in particular. Just a label for which sheet it came from. Any ideas?

[JBeaucaire]

Yes, post a sample workbook with several of these sheets and a complete list of the keywords you want to search for.

Click GO ADVANCED and use the paperclip icon to post up a copy of your workbook.

[HSG4L]

Changed the values in a lot of the cells due to the nature of the information. As well, the headings in each sheet is different because the workbook I get everyweek is like that. There is a different sized logo on top for each port.

Here would be an example list of keywords I would need to go through: "Clinked, Iron, Alum, South Africa, China, India). Any rows in any of those sheets that have any of those words should be copied to a new sheet and then put into a list with a new column made to the side of it to identify which sheet it came from.

All the rows in the new sheet don't have to line up perfectly, some can have more columns than others. No need for each column header either as since I know which sheet it's from, if I am unsure what the number is, I can look at the sheet to see "Oh, it's the quantity or its the ETA time."

Thanks!

[JBeaucaire]

This should do it:

Please Login or Register  to view this content.