|
- Sub splitdata()
- Dim arr(), re(), columnheaders(), tmp(1)
- Dim i&, j%, k&, cnt&, yyb%
- Dim d As Object
- With ThisWorkbook.Sheets("明细")
- i = .Cells(Rows.Count, 1).End(xlUp).Row
- j = .Cells(1, Columns.Count).End(xlToLeft).Column
- arr = .Range(.Cells(1, 1), .Cells(i, j)).Value
- columnheaders = .Range(.Cells(1, 1), .Cells(1, j)).Value
- yyb = .Range("1:1").Find("营业部", , , xlWhole).Column
- End With
-
- Set d = CreateObject("scripting.dictionary")
- For i = 2 To UBound(arr)
- d(arr(i, yyb)) = d(arr(i, yyb)) & "|" & i
- Next
- Application.ScreenUpdating = False
- Application.DisplayAlerts = False
- tmp(1) = d.items
- Set d = Nothing
- For k = 0 To UBound(tmp(1))
- tmp(0) = Split(tmp(1)(k), "|")
- ReDim re(1 To UBound(tmp(0)), 1 To UBound(arr, 2) + 1)
- For i = 1 To UBound(tmp(0))
- For j = 1 To UBound(arr, 2)
- re(i, 1) = i
- re(i, j + 1) = arr(tmp(0)(i), j)
- Next
- Next
- Workbooks.Add (1)
- Range("A1") = "序号"
- Range("B1").Resize(1, UBound(arr, 2)) = columnheaders
- Range("A2").Resize(UBound(re), UBound(re, 2)) = re
- ActiveWorkbook.SaveAs ThisWorkbook.Path & "" & re(1, yyb + 1), 51
- ActiveWorkbook.Close
- Next
- Application.DisplayAlerts = True
- Application.ScreenUpdating = True
- End Sub
复制代码 加个根据营业部数据做的乱序分类拆分工作簿的代码,看看行不行。 |
|