Need macro that searches for a description based on a number from a drop down list

Hello all,

First time poster here and VERY new to VBA. I am requiring a macro that searches roughly 60 (yes 60 lol) worksheets in a workbook that returns the text from the correct fields of each worksheet into a summary sheet.
For example:

the # 101 may be in some, none, or all of the worksheets. The description in the cell beside it would be the reason it is there (ex 101 is a truck that broke down, reason being the diff pressure was high). I need a macro that when a truck # is picked from a drop down box, someone can press a search button, the macro then searches the worksheets for that truck #, and for each instance it finds that truck #, it then copies and puts the reason for breaking down into a summary.

I realize I may be vague with some of this so forgive me. Thank you in advance for any help that may be provided.

xladept,

Sorry for the super late response in getting this to you. I just took one of the worksheets from within the workbook to use as a sample.

On sheet 3 of the sample, you'll find the info I used for the code in the Summary worksheet. The listed date names are the names in the workbook (the date and letter associated change week by week, month by month).

So what I am hoping to do is use a search button that scrubs the worksheets to search for and paste the truck #, and the reason it is down. If at all possible, to also paste what category it was under.

ex. Condition Based Monitoring or Komatsu Breakdowns etc. (The headings for each category don't stay on the same line either due to adding and removing lines based on the volume during the day)

Thanks again for any help you are able to provide

I would prefer a button, for I won't be the main one using this, so it'd be nice to have a user friendly button that someone can just press if need be. I'm just currently trying to figure out how to work the macro you just gave me. As I said, I'm so new it's stupid .

You sir are a genius :D. Are you at all able to alter the code in order to pull the description of the breakdown?. Currently it is showing the location as to where it broke down. (column D), where I am looking for the reason which is located in F.

And am I able to copy the code and just slap it into the entire workbook? Thanks again so much for your help and patience :D. Really is appreciated man

Thanks again so much for the help. Really is greatly appreciated. I had a couple other questions regarding the information it pulls, but I have bugged you long enough :D. Thanks for everything xladept!

Okay, well I will post my issues I'm having. Don't feel obligated to reply cause you've already done enough:

The reason for breakdown & category isn't being copied on every instance (even if there is one in the field).

And if the number of a truck happens to be part of a work order number on the right hand side (ex: work order number is 30574113), it pulls a truck # instance if I was searching for truck 113 for ex.

Hello Peebly,

Can you please attach the workbook with the working VBA. It will be very helpful.
Thank you!

Here is my entire december workbook with the VBA in it. I did notice as well xladept, that it seems to be falsely reporting some trucks as well. The easiest example is if you do a search for truck 219, it shows a description, etc for it. But shows that it is in the "Field Call Data" sheet, and not from the "Dec 31 I2 NS" sheet I located it in. So I am having that issue as well I noticed, as well as the previously posted ones.

Seems to have addressed the issue. On the rare occasion, is still seems to be putting the truck location in the reason column, and not pasting the reason. (219 being a good example again. for Dec 9, it shows "NSE" as the reason. That however is the location, yet the reason on that day wasn't pulled). Not sure if that is a quick fix or not.

I also haven't tested a lot of trucks so far, but I seem to have a lockup issue when I tried pulling truck 108. It just seems to hang there after posting a couple results.

Xladept, once again thank you for your patience and help in this matter. Everything looks perfect now, and I see no issues so far with locking up. I'll mark this thread as solved. Wish I had 9 stars to click for ya!. Thanks again.

Heya It works, but in the process also removes a few things. When I input the truck number, it pulls the work order and places it beside the truck number, as well as pulling the work order number into the seperate column I created.

Also, it no longer pulls the Category anymore. Not sure if that's just happening on my end or not

Well I just want it to show how it did previously, just adding the workorder column. OHHHHHH hey, is there a way to make each one LINK to the worksheet it's from?. So if I click the link, it goes to that entry??. That'd be sick

Oh, I also noticed it pulls the WO and puts it beside the truck number as well as in it's own column. Is there a way to correct that so the work order only shows in the column next to Category?