|
本帖最后由 全党 于 2024-5-21 21:01 编辑
- Sub shishi()
- Dim d As Object, ar, i&, wb As Workbook, sht As Worksheet, brr()
- Set d = CreateObject("scripting.dictionary")
- Application.ScreenUpdating = 0
- Application.DisplayAlerts = 0
- p = ThisWorkbook.Path & "\分表"
- f = Dir(p & "*.xlsx")
- Do While f <> ""
- Set wb = Workbooks.Open(p & f, 0)
- nj = Left(f, 3)
- For Each sht In wb.Sheets
- If sht.Name Like "*统计" Then
- ar = sht.UsedRange
- For i = 3 To UBound(ar)
- If Len(ar(i, 15)) And ar(i, 15) <> 0 Then
- Key = ar(i, 15) & "," & nj & "," & ar(i, 2)
- If Not d.exists(key) Then
- d(Key) = Array(ar(i, 20), ar(i, 21), ar(i, 22), ar(i, 23))
- End If
- End If
- Next
- End If
- Next
- wb.Close 1
- f = Dir
- Loop
- ReDim brr(1 To d.Count, 1 To 7)
- With Sheet1
- .UsedRange.Offset(1).Clear
- For Each Key In d.keys
- n = n + 1
- tt = Split(Key, ","): ss = d(Key)
- brr(n, 1) = tt(0): brr(n, 2) = tt(1): brr(n, 3) = tt(2)
- For j = 0 To 3
- brr(n, j + 4) = ss(j)
- Next
- Next
- .Range("a2").Resize(n, 7) = brr
- End With
- Application.ScreenUpdating = 1
- Application.DisplayAlerts = 1
- End Sub
复制代码
|
|