不同工作表(同一单元格)中的总和值

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"说需要Object…

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

Answer 2

下面是一个非常简单易懂的程序来说明VBA如何用于范围内的循环。如果您有任何问题，请随时提问:

Sub SumPAF()
'Save a reference to the Summary Sheet
Dim SummarySheet As Worksheet
Set SummarySheet = Sheets("Summary PAF")

'Save a reference to the Summary Table and decide the table dimensions
Dim SummaryTable As Range
Set SummaryTable = SummarySheet.Range("A1:F20")

'Create an array to save the sum values
Dim SumValues() As Double
ReDim SumValues(1 To SummaryTable.Rows.Count, 1 To SummaryTable.Columns.Count)

'Loop through the workbook sheets
Dim ws As Worksheet, TableRange As Range
For Each ws In ActiveWorkbook.Worksheets
'Find sheets ending in PAF other than the summary PAF
If ws.Name Like "*PAF" And Not ws.Name = SummarySheet.Name Then
'create a reference to a range on the sheet in the same place and dimensions as the summary table
Set TableRange = ws.Range(SummaryTable.Address)

'loop through the range, cell by cell
Dim i As Long, j As Long
For i = 1 To TableRange.Rows.Count
For j = 1 To TableRange.Columns.Count
'Sum each cell value into the array, where its cell address is the array coordinates.
SumValues(i, j) = SumValues(i, j) + TableRange.Cells(i, j).Value
Next j
Next i
End If
Next

'Output the array into the summary table
SummaryTable.Value = SumValues
End Sub