CODEWITHSUNDEEP
excelvbaexcel-formula
user2378895
user2378895

Reputation: 431

Sum same cell across multiple workbooks

I have weekly timesheets in separate workbooks. At the end of the month, I need to sum up all of the hours across all workbooks when I create monthly invoice. The sheet name and cell address is the same across all workbooks. The sheet name is 'Weekly ACT Rpt Billable'. The total hours are stored in cell I21, so the address is 'Weekly ACT Rpt Billable'!$I$21.

I want a macro that sums all 'Weekly ACT Rpt Billable'!$I$21 across all open workbooks.

Here's what I have so far. It works for 1 workbook but not for more than 1 open workbook

Sub SumCellOpenWorkbooks()
    Dim iWbCount As Integer, sSumAddress As String
    
    For iWbCount = 1 To Workbooks.Count
        
        If Not (Workbooks(iWbCount).Name = "PERSONAL.XLSB" Or Workbooks(iWbCount).Name = "Book1") Then
            sSumAddress = "'[" & Workbooks(iWbCount).Name & "]" & "Weekly ACT Rpt Billable'!" & "$I$21"
            Debug.Print sSumAddress
            ActiveSheet.Range("A1").Formula = "=sum(" & sSumAddress & ")"
        End If
    Next
    
End Sub

Upvotes: 1

Views: 393

Answers (1)

Wils Mils
Wils Mils

Reputation: 633

How about this?

For iWbCount = 1 To Workbooks.Count
        
    If Not (Workbooks(iWbCount).Name = "PERSONAL.XLSB" Or Workbooks(iWbCount).Name = "Book1") Then
        If sSumAddress = "" Then
            sSumAddress = "'[" & Workbooks(iWbCount).Name & "]" & "Weekly ACT Rpt Billable'!" & "$I$21"
        Else
            sSumAddress = sSumAddress & ",'[" & Workbooks(iWbCount).Name & "]" & "Weekly ACT Rpt Billable'!" & "$I$21"
        End If
        Debug.Print sSumAddress
    End If
Next
ActiveSheet.Range("A1").Formula = "=sum(" & sSumAddress & ")"

Upvotes: 3

Related Questions