|
参与一下。。。- Sub ykcbf() '//2023.12.30
- Application.ScreenUpdating = False
- Application.DisplayAlerts = False
- Dim tm: tm = Timer
- Dim arr, brr, b, d
- Set d = CreateObject("scripting.dictionary")
- Set fso = CreateObject("Scripting.FileSystemObject")
- p = ThisWorkbook.Path & ""
- p1 = p & "拆分后效果"
- If Not fso.FolderExists(p1) Then fso.CreateFolder p1
- Set sh = ThisWorkbook.Sheets("收入")
- c = 2 '//拆分列号
- b = [{1,2,3,4,9}]
- With sh
- r = .Cells(.Rows.Count, c).End(xlUp).Row
- arr = .Range("a1:j" & r)
- End With
- For i = 2 To UBound(arr)
- s = arr(i, c)
- If Not d.exists(s) Then Set d(s) = CreateObject("Scripting.Dictionary")
- d(s)(i) = i
- Next
- On Error Resume Next
- For Each k In d.keys
- m = 0
- ReDim brr(1 To UBound(arr), 1 To 5)
- Application.SheetsInNewWorkbook = 1
- Set wb = Workbooks.Add
- With wb.Sheets(1)
- .Name = k
- .[a1:e1] = Array("序号", "公司", "姓名", "基薪", "补发")
- For Each kk In d(k).keys
- m = m + 1
- brr(m, 1) = m
- For j = 2 To UBound(b)
- brr(m, j) = arr(kk, b(j))
- Next
- Next
- .[a2].Resize(m, 5) = brr
- With .[a1].Resize(m + 1, 5)
- .Borders.LineStyle = 1
- .HorizontalAlignment = xlCenter
- .VerticalAlignment = xlCenter
- .EntireColumn.AutoFit
- End With
- End With
- wb.SaveAs p1 & k
- wb.Close
- Next
- Set d = Nothing
- Application.ScreenUpdating = True
- MsgBox "拆分完毕,共用时: " & Format(Timer - tm, "0.000秒"), , "提示"
- End Sub
复制代码
|
评分
-
1
查看全部评分
-
|