Sub AwTest()
Dim i&, j%, x&, r&, y%, s, xmStr$, ws As Worksheet
Dim arr, brr(1 To 5000, 1 To 14), d As Object
Set d = CreateObject("Scripting.Dictionary")
Sheet1.Activate
For Each ws In Sheets
If ws.Name <> ActiveSheet.Name Then
arr = ws.Range("A1:M" & ws.Cells(Rows.Count, 1).End(3).Row)
For i = 2 To UBound(arr)
If Len(arr(i, 1)) Then
xmStr = arr(i, 1) & "|" & arr(i, 2) & "|" & arr(i, 3)
For j = 4 To UBound(arr, 2)
x = d(xmStr): y = d(xmStr & "|c")
If arr(i, j) > 0 Then
If x = 0 Then r = r + 1: d(xmStr) = r: brr(r, 1) = arr(i, 1): _
brr(r, 2) = arr(i, 2): brr(r, 3) = arr(i, 3): x = r
If y = 0 Then y = 4: d(xmStr & "|c") = y
brr(x, y) = arr(1, j)
brr(x, y + 5) = arr(i, j)
d(xmStr & "|c") = d(xmStr & "|c") + 1
s = s + arr(i, j)
End If
Next
If x > 0 Then brr(x, UBound(brr, 2)) = s: s = 0
End If
Next
End If
Next
[a2:n5000].ClearContents
[a2].Resize(r, 14) = brr
End Sub
|