Compiling Data on Multiple Excel Tabs

[derekstewart]

I have 30 files each with 16 tabs marked 1-16. On each of the tabs, Cell Q6 contains a number I to compile and average. So I need to grab cell Q6 in all 30 files on tab1 and get a tab 1 average, then a tab2 avg, etc. Is there a good way to do this? I tried to do a macro, but the names of the 30 files are different everytime so the macro didn't work. My plan was to start another excel sheet with 1-16 in the columns. I would grab the data, which would populate the numbers for the 16 tabs then insert a line, moving them down so the macro could populate the now empty cells for the next sheet. Any thoughts. Should I just rename the files each time? I guess that would work, but it just seems like there should be an easier way. Any thoughts? Thanks. D

 

[SkipVought]

derekstewart

Please take this post to the VBA forum707.

ALSO, please post the macro that you discovered would not work, because it CAN be made to work. It is EXACTLY the way to begin!

Skip,

_{[red]Be advised:[/red]To be safe on the FOURTH, don't take a FIFTH on the THIRD, or...
You might not come FORTH on the FIFTH!}