VBA function (i.e., Sum expenditures over multiple worksheets in one workbook into another workbook)

[David1385]

I need to sum the expenditures of multiple projects defined under several worksheets. Each project is named on the worksheet tab.

The first workbook contains the expenditures per month per category. The categories are "Labor" and "Material". The expenditures (i.e., under the first workbook) for labor and material are under C69:AS69 and C84:AS84, respectively.

The second workbook is where I would like the results to be output. Basically, I would like to define the worksheets of interest under A6:A16, the categories of interest under B6:B12, and the months of interest under B25:E64 . Once I define the worksheet and categories under the aforementioned columns/rows, the results could be output into E25:E64 and F25:F64 for labor and material, respectively, right next to the corresponding months defined on B25:E64. The results need to sum all values under the same category under the correct month.

Please, keep in mind, coding isn't what I do for living. Below is the code:

Sub CalculateExpenditure()
    Dim wb1 As Workbook ' First workbook ("Expenditures.xlsx")
    Dim wb2 As Workbook ' Second workbook ("Budget Expensing Chart.xlsx")
    Dim ws1 As Worksheet ' Worksheet in the first workbook
    Dim ws2 As Worksheet ' Worksheet in the second workbook
    Dim month As String
    Dim category As String
    Dim sumValue As Double
    Dim outputCell As Range
   
    ' Set references to the workbooks
    Set wb1 = Workbooks("Expenditures.xlsx")
    Set wb2 = Workbooks("Budget Expensing Chart.xlsx")
   
    ' Set references to the specific worksheets
    Set ws1 = wb1.Worksheets("Expense_job1")
    Set ws2 = wb2.Worksheets("Results")
   
    ' Get the values of the month and category from the second workbook
    month = ws2.Range("B25").Value
    category = ws2.Range("B6").Value
   
    ' Find the column with the specified month in the first workbook
    Dim startYear As Integer
    Dim startMonth As Integer
    Dim monthColumn As Range
   
    startYear = 2019 ' Starting year for expenditure columns in the first workbook
    startMonth = 6 ' Starting month for expenditure columns in the first workbook
   
    ' Calculate the number of months from the starting month/year to the specified month/year
    Dim numMonths As Integer
    numMonths = (Year(DateValue(month & " " & startYear & " ")) - startYear) * 12 + (month(DateValue(month & " " & startYear & " ")) - startMonth) + 1
   
    ' Set the range for the specified category based on the number of months
    Dim expenditureRange As Range
   
    Select Case category
        Case "Labor"
            Set expenditureRange = ws1.Range(ws1.Cells(66, 3), ws1.Cells(66, 3).Offset(0, numMonths - 1))
            Set outputCell = ws2.Range("E25")
        Case "Material"
            Set expenditureRange = ws1.Range(ws1.Cells(81, 3), ws1.Cells(81, 3).Offset(0, numMonths - 1))
            Set outputCell = ws2.Range("F25")
        ' Add more cases for additional categories if needed
        Case Else
            Exit Sub ' Exit the sub if the category is not valid
    End Select
   
    ' Calculate the sum of expenditures for the specified month and category
    sumValue = Application.WorksheetFunction.Sum(expenditureRange)
   
    ' Output the sum to the corresponding cell in the second workbook
    outputCell.Value = sumValue
End Sub

EDIT:
I forgot to mention, the code isn't working properly. Can someone take a look at it.

I'm sure I did not define properly where the values will be coming from, as in month and sheet name of interest.

 

[sijpie]

A number of things I don't understand having quickly scanned your code:

You say the totals row is row 69 (labour) and 84 (material).
yet in your code it gathers the data from row 66 and 81.

You mention that the months it needs to look at are in cells B25:E64.???? should that be B25:B64?

In that case your code would have to loop through these rows.

It will help greatly if you can post part of the worksheet (input and output) with some dummy data or dummy headings.