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