Excel – How to get Excel to pull data from multiple workbooks from exact dates based on an entered date

microsoft excelmicrosoft-excel-2007

I am running windows-7 and using Excel 2007 Enterprise.

I am a truck driver and need to catalog my pay based on a changing day to day system. I am paid per mile, per stop, per hour, or per flat rate depending on the type of route.

I have made a workbook that uses =IF() formulas to determine what pay scale to use and calculates how much I am to be payed automatically. As a result each workbook has 4 sheets, 1 sheet per trip as any more than 4 is so rare it may only happen once per year.

Sheet 1 pulls data from sheets 2, 3, and 4 automatically and fills in the correct cells and then recalculates my pay depending on what data is entered.

Now that I have a workbook for each day, as I can't combine days for several reasons not worth explaining here, I must have a total for each week to compare my gross earnings to. I have another workbook just for this reason, however I can't seem to get my weekly pay to pull data from my daily pay sheets as they are separate workbooks. I need my weekly pay to pull all total pay cells and total them on one sheet.

The cell in the daily pay is "B23 on Sheet-1", this is the location on all workbooks. I know this is a lot of information by I hope that it helps you understand what I need. So for what I need: I want to input the date that I am to be paid and have the workbook find the days needed to reference based on that date ie: 11/15/13 is pay for dates 11/03/13 – 11/08/13". now that it has the dates, find value in cell B23 on sheet-1 of all 7 dates and show it in this workbook. I can then have it compare my gross pay to what I should be paid if it is different, this part I already know how to do and already have it set up. Thank you in advance for your help.

Best Answer

Take a look at this and make sure you replace the path with the path on your computer where you keep your files.

How it works: Place this in a module in a new workbook, put the payday in cell A1 and run the macro called Total. The dates and pay amounts will show up in columns B and C with the sum in C7.

Option Explicit
Sub Total()
Application.ScreenUpdating = False
Call getdates
Call openWBgetData
Range("c7") = [sum(C1:C6)]
Application.ScreenUpdating = True
End Sub

Private Sub getdates()
Dim i As Integer, j As Integer
j = 7

For i = 1 To 6
    Cells(i, 2) = Cells(1, 1).Value - j
    j = j + 1
Next

End Sub

Private Sub openWBgetData()
Dim k As Integer
Dim lng As Long
Dim path As Long
Dim wbname As String
path = "C:\path\to\"

For k = 1 To 6
    wbname = Cells(k, 2).Value
    Application.Workbooks.Open (path & wbname & ".xlsx")
    lng = Workbooks(wbname).Sheets("Trip-1").Range("O6").Value
    Workbooks(wbname).Close
    Cells(k, 3) = lng
Next


End Sub
Related Question