Pulling Data from worksheets in multiple workbooks to creat a pivot table.

[BadKornFlake]

We have a trucking company that daily we input multiple driver's payroll. Part of the data we input is the miles driven, gallons of fuel purchased, and cost of that purchase for the driver for that day. We collect this information per driver per week and then name the workbook by the date of the week ending (i.e. 12-5-13) The last worksheet of that workbook is "mileage report" where I use a vlookup to store all the raw data of mileage, gallons, and cost. (see attachment "dataworkbook112-5-13.xlsx").
At the end of the month we take the "mileage report" worksheet from each week's workbook. Copy all the data and paste it to a new workbook. One week under the other so the data of each header column lines up. Then I make a pivot table of the data and then add calculations to find the MPG and the cost per gallon. (see attachment "Mileage Recap.xlsx"). Each month I delete the raw data page and cut and paste all over again and then refresh the pivot table.
I have read, googled, and youtubed till Im blue in the face. I want to be able to leave the mileage report worksheet in the original workbooks and have the pivot table go and pull the information to create the table.
Just to note. The worksheet name, headers, column and rows are the constant. The only thing that changes from week to week is the data's workbook name. (12-5-13, 12-19-13, etc.)

Thank you in advance for any help.

[Tinbendr]

Have you tried Microsoft Query? It will pull in data from another workbook.

Data->From Other Source->From Microsoft Query->Excel files...

[BadKornFlake]

It is a step in the right direction but its only giving me the first worksheet.

[Tinbendr]

And can you not create another query underneath the first one. (And I'm just guessing at this point. I've only used query a time or two.)

A macro could be written to pull the data in, but that will take a little time. (I'm already on two projects. )

You might give Ron DeBruin's site a check to see if his merge page will fill your requirement.