Excel多PAF后缀工作表相同单元格批量求和VBA代码报错（Object is required）求助

Hey there! Let's fix that VBA error and get your summary working properly. Here's what's going wrong and how to fix it step by step:

1. Syntax Error: Missing Parentheses for the Sub Procedure #

Your Sub definition is missing parentheses—VBA requires procedure definitions to include them even when there are no parameters. This is likely one of the direct causes of the "Object is required" error. It should be:

Sub SumPAF()

2. Incorrect Worksheet Name Matching #

Your current condition ws.Name Like "PAF" only matches a worksheet named exactly "PAF". Since you need all sheets ending with PAF, use the wildcard * to match any preceding characters. Also, we need to exclude the "Summary PAF" sheet itself to avoid adding its own data into the sum. Update the condition to:

If ws.Name Like "*PAF" And ws.Name <> "Summary PAF" Then

3. Broken Range Reference in the Sum Calculation #

Your original sum line Range("E10") = WorksheetFunction.Sum(Range("E10")) just replaces E10's value with itself—it never references the cells from the other PAF sheets. You need to explicitly pull values from the current loop's worksheet and add them to the summary sheet's cells.

Also, relying on Activate is risky and prone to errors—we'll directly reference the summary sheet object instead.

Fixed Full Code (Supports 6 Columns x 20 Rows) #

This version handles your full table range, clears old data to avoid duplicate sums, and handles empty cells gracefully:

Sub SumPAF()
    Dim ws As Worksheet
    Dim summaryWs As Worksheet
    Dim rowNum As Integer
    Dim colNum As Integer
    
    ' Directly reference the summary sheet (no need to activate)
    Set summaryWs = ThisWorkbook.Worksheets("Summary PAF")
    
    ' Clear existing summary data to prevent double-counting
    summaryWs.Range("A1:F20").ClearContents
    
    ' Loop through all worksheets in the workbook
    For Each ws In ThisWorkbook.Worksheets
        ' Target only PAF-ending sheets (excluding the summary itself)
        If ws.Name Like "*PAF" And ws.Name <> "Summary PAF" Then
            ' Loop through 20 rows and 6 columns to accumulate values
            For rowNum = 1 To 20
                For colNum = 1 To 6
                    ' Add values, treating empty cells as 0 to avoid errors
                    summaryWs.Cells(rowNum, colNum).Value = _
                        summaryWs.Cells(rowNum, colNum).Value + _
                        IIf(IsEmpty(ws.Cells(rowNum, colNum)), 0, ws.Cells(rowNum, colNum).Value)
                Next colNum
            Next rowNum
        End If
    Next ws
End Sub

Quick Notes #

• The code uses ThisWorkbook instead of ActiveWorkbook to ensure we're always working with the workbook containing the VBA code (safer if you have other workbooks open).
• The IIf function handles empty cells so they don't cause calculation errors.
• We clear the summary range first, so each run starts fresh instead of adding to old totals.

内容的提问来源于stack exchange，提问作者CarmenV