|
Sub 拆分()
On Error Resume Next
Set Rng = Application.InputBox("请选择拆分的字段", "选择", , , , , , 8)
If Rng Is Nothing Then Exit Sub
B = MsgBox("是否拆成独立工作簿?", 4 + 32 + 256, "设定")
Application.ScreenUpdating = False
Application.DisplayAlerts = False
For I = Sheets.Count To 2 Step -1
Sheets(I).Delete
Next
Set D = CreateObject("scripting.dictionary")
ARR = Range("A2").CurrentRegion
CL = Rng.Column
For I = 3 To UBound(ARR)
If Not D.exists(ARR(I, CL)) Then
D(ARR(I, CL)) = I
Else
D(ARR(I, CL)) = D(ARR(I, CL)) & "," & I
End If
Next
K = D.KEYS
For I = 0 To UBound(K)
Sheets.Add(after:=Sheets(Sheets.Count)).Name = K(I)
ThisWorkbook.Sheets(1).Range("A1:I2").Copy ActiveSheet.Range("A1")
W = Split(D(K(I)), ",")
For J = 0 To UBound(W)
ThisWorkbook.Sheets(1).Rows(W(J)).Copy ActiveSheet.Range("A65536").End(3)(2)
Next
ActiveSheet.Columns("A:I").Columns.AutoFit
If B = 6 Then
Sheets(K(I)).Copy
ActiveWorkbook.SaveAs ThisWorkbook.Path & "\" & K(I) & ".xls"
ActiveWorkbook.Close
Sheets(K(I)).Delete
End If
Next
Application.DisplayAlerts = True
Application.ScreenUpdating = True
MsgBox "拆分完成!"
End Sub |
|