|
按楼主要求改了一下。
- Sub ykcbf() '//2024.4.30
- Application.ScreenUpdating = False
- Application.DisplayAlerts = False
- Set d = CreateObject("Scripting.Dictionary")
- Set ws = ThisWorkbook
- p = ws.Path & ""
- Set Sh = ws.Sheets("汇总表格")
- bt = 1: col = 1
- With Sh
- r = .Cells(Rows.Count, col).End(3).Row
- c = .UsedRange.Columns.Count
- arr = .[a1].Resize(r, c)
- End With
- For i = bt + 1 To UBound(arr)
- s = CStr(arr(i, col)): ss = Replace(arr(i, 2), "/", "-")
- If Not d.exists(s) Then Set d(s) = CreateObject("Scripting.Dictionary")
- If Not d(s).exists(ss) Then Set d(s)(ss) = CreateObject("Scripting.Dictionary")
- d(s)(ss)(i) = Application.Index(arr, i)
- Next
- On Error Resume Next
- For Each k In d.keys
- Sh.Copy
- Set wb = ActiveWorkbook
- n = 0
- For Each kk In d(k).keys
- n = n + 1
- m = d(k)(kk).Count
- Set wb1 = Workbooks.Add
- If n = 1 Then
- Set sht = wb.Sheets(1)
- Else
- Sh.Copy After:=wb.Sheets(wb.Sheets.Count)
- Set sht = wb.Sheets(wb.Sheets.Count)
- End If
- With sht
- .Name = kk
- .DrawingObjects.Delete
- .UsedRange.Offset(bt + m).Clear
- .Cells(bt + 1, 1).Resize(m, UBound(arr, 2)) = Application.Rept(d(k)(kk).Items, 1)
- .Cells.Copy wb1.Sheets(1).[a1]
- End With
- With wb1
- .Sheets(1).Name = kk
- fn1 = Trim(.Sheets(1).Cells(2, 3))
- .Sheets(1).Columns(5).Delete
- .Sheets(1).Range("A:C").Delete
- .SaveAs Filename:=p & Format(kk, "yyyy年m月d日-") & fn1 & ".TXT", FileFormat:=xlUnicodeText
- .Close 1
- End With
- Next
- wb.SaveAs p & k, 51
- wb.Close 1
- Next
- Set d = Nothing
- Application.ScreenUpdating = True
- MsgBox "拆分完毕!"
- End Sub
复制代码
|
|