Code to return 2nd highest value from each tab of another workbook to current workbook

Hello! I'm extremely new to macros and codes and have only run a few successfully. I apologize for any glaring mistakes! This is also a repost since my original post seems to be locked (I forgot to utilize code tags the first time around).

I'm trying to come up with a code to return the 2nd highest value from the "D" column of each tab of another workbook to my current workbook. Please see my code below. I pieced it together from other codes that I've used in the past and another code that I found online. I keep getting errors left and right when I try to alter the code. When I try to use the one below, I get a "Compile Error: Type Mismatch" popup. Can anyone help me fix this code/come up with a better one? Thank you!

Please Login or Register  to view this content.

Originally Posted by craftyocelot

I'm trying to come up with a code to return the 2nd highest value from the "D" column of each tab of another workbook to my current workbook.

Try this. It's another way of getting the second highest value.

Please Login or Register  to view this content.

Replace your_workbook.xls with the name of the workbook you want to retrieve the second highest value in Column D from. (You should probably use the full path.)
Replace your_sheetname with the name of the first sheet in the workbook, that you need the value from.

The second highest value should then be listed in Cell D3.

This worked for me with my test data. If that works for you, you can duplicate it for the other sheets in that same workbook, and list those values wherever you want.

Thank you! Is there a way to have it automatically draw the data from every sheet in each workbook when I run the macro? I have years of data to go through and luckily they're all in the same format. However each year has its own workbook and each month within each workbook has its own sheet.

I'm about to step out the door so this will be quick.

The answer to your question is most certainly. If you have a whole bunch of workbooks, and a bunch of sheets within each workbook, then yes, your original idea of looping through each worksheet in each of the workbooks is best. Otherwise you'd have to hard-code everything. (Hardcode all of the workbook names and hard-code all of the worksheet names.) You really don't want to do it that way.

A double next FOR NEXT loop should work, something like:

Please Login or Register  to view this content.

A FOR EACH type of loop works too. (That's often just a matter of preference.)

I'm out of the office right now but will definitely try this when I can and report back. Thank you so much!

I can post the entire code when I get home this evening, assuming no one else beats me to it.

bakerman, that looks very good. Good work. It's also probably a few lines less code than what I would have come up with. Hopefully that will work for crafty.

However each year has its own workbook and each month within each workbook has its own sheet.

I wonder if it would be handy to have the year (workbook name?) and month (sheet name?) in cells beside each 2nd highest value?
I don't have to wonder however about bakerman2 being able to change his code slightly to accommodate that if indeed wanted!!!!!!!

Isn't it a little early in the morning there to have a warm heart?
Saying that, it depends on where you're at. Could be a warm pub on the Keyserlei.
BTW, a reputation was added. That was long overdue

Apologies for the extremely delayed response. I was away for a conference and just remembered I had this project on the back burner.

I just tried to run the macro but nothing seemed to have happened. I changed the file path for mydir to that of the file that I'm trying to extract the data from, saved, tried to run the macro in my main workbook, and nothing. No error box. Nothing. Again I'm really new at this so please feel free to explain it to me like I'm 5. >.>

Thank you!

Also gave Rep+ for the effort. :]

Sorry for the jumbled parameters! I have seven years of data to go through so I have seven files. Each year's file/workbook has twelve sheets (one for each month of that fiscal year). I also just realized the column doesn't matter for this so really I just need to obtain the second highest number found in each sheet.

My current workbook has a sheet for each fiscal year I'm reviewing. I was hoping for a macro that would grab the second highest number from each sheet in a fiscal year workbook and spit it out into the sheet that I have open. I figured I would just have to switch out the fiscal year file name in mydir for each fiscal year file but I may just be misunderstanding something very simple. :[