excel - 不同工作表中的总和值(相同单元格)

Question

我有一个包含多张工作表的工作簿，每个项目的工作表数量可以改变，但它们都以 PAF 结尾。该表格在所有工作表以及单元格中都是相同的。
我有一个带有完全相同表格的摘要选项卡，我只需要将所有内容汇总起来，表格至少有 6 列和 20 行，所以每个单元格都需要相同的公式(基本上)所以我想出了以下但我收到一个错误。有什么建议么？

Sub SumPAF
Dim ws as Worksheet
Sheets("Summary PAF").Activate

For Each ws In ActiveWorkbook.Worksheets
If ws.Name Like "PAF" Then

Range("E10") = WorksheetFunction.Sum(Range("E10"))

End If
Next
End Sub

它卡在“For Each”中，说需要一个对象......

Answer 1

我已经对代码进行了注释，因此您理解它应该没有问题。

Option Explicit

Sub SumPAF()
    Dim ws As Worksheet
    
    '~~> This will store the cell addresses
    Dim sumFormula As String
    
    '~~> Loop though each worksheet and check if it ends with PAF
    '~~> and also to ingore summary worksheet
    For Each ws In ActiveWorkbook.Worksheets
        If UCase(ws.Name) Like "*PAF" And _
        InStr(1, ws.Name, "Summary", vbTextCompare) = 0 Then _
        sumFormula = sumFormula & "," & "'" & ws.Name & "'!E10"
        '~~> or simply 
        'sumFormula = sumFormula & ",'" & ws.Name & "'!E10"
    Next
    
    '~~> Remove the intital ","
    sumFormula = Mid(sumFormula, 2)
    
    '~~> Insert the sum formula
    If sumFormula <> "" Then _
    Sheets("Summary PAF").Range("E10").Formula = "=SUM(" & sumFormula & ")"
End Sub