|
- Sub 按钮1_Click()
- Dim crr()
- Set d = CreateObject("scripting.dictionary")
- Set dd = CreateObject("scripting.dictionary")
- Set fso = CreateObject("scripting.filesystemobject")
- Application.ScreenUpdating = False
- For Each f In fso.getfolder(ThisWorkbook.Path).Files
- If InStr(f.Name, "每日报表") = 0 Then
- With Workbooks.Open(f)
- arr = .Sheets(1).UsedRange
- .Close False
- End With
- d.RemoveAll
- dd.RemoveAll
- For j = 2 To UBound(arr)
- If Len(arr(j, 2)) > 0 Then
- str1 = Left(arr(j, 7), 2) & "@#$@" & arr(j, 2) & "@#$@" & arr(j, 3) & "@#$@" & arr(j, 4)
- d(str1) = d(str1) + arr(j, 5)
- dd(str1) = dd(str1) + arr(j, 6)
- End If
- Next j
-
- For Each sht In ThisWorkbook.Sheets
- brr = Split(sht.Name, "、")
- ReDim crr(1 To d.Count, 1 To 5)
- r = 0
- For x = 0 To d.Count - 1
-
- str1 = d.keys()(x)
- For Z = 0 To UBound(brr)
- If Left(str1, 2) = brr(Z) Then
- arr = Split(str1, "@#$@")
- r = r + 1
- crr(r, 1) = arr(1)
- crr(r, 2) = arr(2)
- crr(r, 3) = arr(3)
- crr(r, 4) = d(str1)
- crr(r, 5) = dd(str1)
- End If
- Next Z
- Next x
- For i = 1 To r
- For j = i + 1 To r
- If crr(j, 5) > crr(i, 5) Then
- For x = 1 To 5
- tmp = crr(i, x)
- crr(i, x) = crr(j, x)
- crr(j, x) = tmp
- Next x
- End If
- Next j
- Next i
- If r > 0 Then
- nm = Left(f.Name, 2)
- If nm = "总店" Then
- sht.[b4].Resize(WorksheetFunction.Min(r, 15), 5) = crr
- Else
- For x = 8 To sht.UsedRange.Columns.Count Step 6
- ww = Left(sht.Cells(2, x), 2)
- If ww = nm Then
- sht.Cells(4, x).Resize(WorksheetFunction.Min(r, 15), 5) = crr
- Exit For
- End If
- Next
- End If
- End If
- Next sht
-
- End If
- Next f
- Application.ScreenUpdating = True
- End Sub
复制代码 |
|