- Sub 拆分()
- Dim arr, brr, i, j, r, d, n
- Application.ScreenUpdating = False
- Application.DisplayAlerts = False
- Set d = CreateObject("scripting.dictionary")
- p = ThisWorkbook.Path & "\分表"
- With Sheet1
- r = .Cells(Rows.Count, 1).End(xlUp).Row
- arr = .Range("a1:i" & r)
- End With
- For i = 2 To UBound(arr)
- s = arr(i, 4) & "-" & arr(i, 5) & "-" & arr(i, 6)
- d(s) = d(s) & "," & i
- Next
- For Each k In d.keys
- n = 1
- xa = Split(d(k), ",")
- ReDim brr(1 To UBound(xa) + 1, 1 To 6)
- For i = 1 To UBound(xa)
- n = n + 1
- For j = 1 To 3
- brr(1, j) = arr(1, j)
- brr(n, j) = arr(xa(i), j)
- Next
- For jj = 7 To 9
- brr(1, jj - 3) = arr(1, jj)
- brr(n, jj - 3) = arr(xa(i), jj)
- Next
- Next
- Set wb = Workbooks.Add
- Set sh = wb.Sheets(1)
- With sh
- .[a:a,b:b,c:c,e:e,f:f].NumberFormatLocal = "@"
- .[a1].Resize(n, 6) = brr
- .Columns("A:G").EntireColumn.AutoFit
- End With
- wb.SaveAs p & k & ".xlsx"
- wb.Close False
- Next
- Application.ScreenUpdating = True
- Application.DisplayAlerts = True
- End Sub
复制代码 |