Sum Values in different worksheets (same cell)

Question

I have a workbook with multiple sheets ,the number of sheets can change for each project but they all end with PAF. The table is the same across all sheets as well as the cells.

I have a summary tab with the exact same table, I just need to sum it all up there, the table has at least 6 columns and 20 rows so each cell would need the same formula (basically) so I came up with the following but I'm getting an error. Any suggestions?

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

It's getting stuck in "For Each" saying that an Object is required...

Answer 1

I have commented the code so you should not have a problem understanding it.

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