the file:
sum.xlsm


How to use an array to traverse the worksheet and summarize them separately? Why does a dialog box that does not match the type pop up after running the code?
Summarize the number of pieces, quantity, price, and freight in each monthly table. code show as below:


Option Explicit

Sub sumallshs()
Dim i, j, p, q
Dim arr, brr
Dim sh As Worksheet
For Each sh In Worksheets
If Trim(sh.Name) Like "*m" Then
p = sh.Cells.Rows.Count
q = sh.Cells(p, 1).End(xlUp).Row
arr = sh.Range("a2:h" & q)
brr = sh.[a2].Resize(UBound(arr) + 1, UBound(arr, 2) + 1)

If q >= 3 Then
For j = 4 To UBound(brr)
For i = 1 To UBound(brr) - 1
brr(UBound(brr), 3) = "total"
brr(UBound(brr), j) = brr(UBound(brr), j) + brr(i, j)
Next i
Next j
sh.[a2].Resize(UBound(brr), UBound(brr, 2)) = brr
End If
End If
Next sh

end sub




After running the code, the following dialog box pops up
20201007075828.jpg