'此段与模拟结果一致
Sub 多簿汇总()
Application.ScreenUpdating = False
Dim f As Object, ar, r%, c%, br&(1 To 8, 1 To 6), xx$, st$
For Each f In CreateObject("Scripting.FileSystemObject").GetFolder(ThisWorkbook.Path).Files
If Right(f.Name, 4) = ".xls" And f.Name <> ThisWorkbook.Name Then
With Workbooks.Open(f)
With .Sheets(1)
xx = Split(Split(Replace(.[a2], " ", ""), "填表人")(0), ":")(1)
st = st & xx & .[d4] & Chr(10)
ar = .Range("d5:i12")
End With
.Close False
End With
For r = 1 To UBound(ar)
For c = 1 To UBound(ar, 2) - 1
If Val(ar(r, c)) Then br(r, c) = br(r, c) + ar(r, c)
Next
Next
End If
Next
For r = 1 To 3
For c = 1 To UBound(br, 2) - 1
If Val(br(r, c)) Then br(r, 6) = br(r, 6) + br(r, c)
Next
br(8, 6) = br(8, 6) + br(r, 6)
Next
Range("d4") = st
Range("d5:i12") = br
Application.ScreenUpdating = True
End Sub |