|
- Sub test1()
- Dim dict As Object, ar, i As Long, j As Long, k
- Dim p As String, s As String, t As String, pos As Long
- DoApp False
- pos = 2
- p = ThisWorkbook.Path & "\分簿分表"
- If Dir(p, vbDirectory) = "" Then MkDir p
- Set dict = CreateObject("Scripting.Dictionary")
- With ActiveSheet
- '.UsedRange.Offset(pos).UnMerge '若有错,用上此句
- ar = .Range("A1").CurrentRegion.Value
- j = UBound(ar, 2)
- For i = pos + 1 To UBound(ar)
- s = ar(i, 1)
- t = ar(i, 3)
- If Not dict.Exists(s) Then Set dict(s) = CreateObject("Scripting.DictionAry")
- If Not dict(s).Exists(t) Then Set dict(s)(t) = .Range("A1").Resize(pos, j)
- Set dict(s)(t) = Union(dict(s)(t), .Range("A" & i).Resize(, j))
- Next
- End With
- For Each k In dict.Keys
- Application.SheetsInNewWorkbook = dict(k).Count
- With Workbooks.Add
- For j = 0 To dict(k).Count - 1
- With .Worksheets(j + 1)
- dict(k).Items()(j).Copy .Range("A1")
- .Name = dict(k).Keys()(j)
- End With
- Next
- .SaveAs p & "\" & k, 51
- .Close
- End With
- Next
- Set dict = Nothing
- Application.SheetsInNewWorkbook = 1
- DoApp
- Beep
- End Sub
- Function DoApp(Optional b As Boolean = True)
- With Application
- .ScreenUpdating = b
- .DisplayAlerts = b
- .Calculation = -b * 30 - 4135
- End With
- End Function
复制代码 |
评分
-
1
查看全部评分
-
|