excel - 跨多个工作簿对同一单元格求和

Question

我在单独的工作簿中有每周时间表。在月底，我需要在创建月度发票时汇总所有工作簿中的所有小时数。工作表名称和单元格地址在所有工作簿中都相同。工作表名称为“Weekly ACT Rpt Billable”。总小时数存储在单元格 I21 中，因此地址为“每周 ACT Rpt Billable”!$I$21。
我想要一个宏来汇总所有“每周 ACT Rpt Billable”!所有打开的工作簿中的 $I$21。
这是我到目前为止所拥有的。它适用于 1 个工作簿，但不适用于超过 1 个打开的工作簿

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

Answer 1

这个怎么样？

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 & ")"