|
- Private Sub CommandButton1_Click()
- Set rng = Range("a1:e1")
- Application.ScreenUpdating = False
- arr = Range("a1:e" & Range("a65536").End(xlUp).Row)
- Set d = CreateObject("scripting.dictionary")
- For i = 2 To UBound(arr)
- If Not d.exists(arr(i, 1)) Then
- Set d(arr(i, 1)) = Cells(i, 1).Resize(1, 5)
- Else
- Set d(arr(i, 1)) = Union(d(arr(i, 1)), Cells(i, 1).Resize(1, 5))
- End If
- Next
- k = d.keys: t = d.items
- For x = 0 To d.Count - 1
- Set wb = Workbooks.Add(xlWBATWorksheet)
- With wb.Sheets(1)
- rng.Copy .[a1]: t(x).Copy .[A2]
- arr = .[a1].CurrentRegion
- n = .Cells(65536, 1).End(xlUp).Row
- For m = 3 To UBound(arr, 2)
- If IsNumeric(.Cells(n, m)) Then
- .Cells(n + 1, 1) = "平均": .Cells(n + 2, 1) = "合计"
- .Cells(n + 1, m) = Application.Average(Application.Index(arr, , m))
- .Cells(n + 2, m) = Application.Sum(Cells(n, m).Resize(n - 1, 1))
- .Columns(m).ColumnWidth = ThisWorkbook.ActiveSheet.Columns(m).ColumnWidth
- End If
- Next
- End With
- wb.SaveAs Filename:=ThisWorkbook.Path & "" & k(x), FileFormat:=xlExcel8
- wb.Close
- Next
- Application.ScreenUpdating = True
- Set rng = Nothing: Set wb = Nothing
- MsgBox "完毕"
- End Sub
复制代码 |
|