|
本帖最后由 lsc900707 于 2017-2-23 20:29 编辑
代码已更改,请再测试效果吧:
Private Sub CommandButton1_Click()
Dim tim1 As Date, tim2 As Date: tim1 = Timer
Dim arr, d As Object, sh As Worksheet
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Set d = CreateObject("scripting.dictionary")
arr = Range("a1:c" & Cells(Rows.Count, 1).End(xlUp).Row)
For i = 2 To UBound(arr)
If Cells(i, 2) <> "" Then
If Not d.exists(arr(i, 1)) Then
Set d(arr(i, 1)) = Range("a" & i).Resize(1, 3)
Else
Set d(arr(i, 1)) = Union(d(arr(i, 1)), Range("a" & i).Resize(1, 3))
End If
End If
Next
x = d.keys
For k = 0 To UBound(x)
Set sh = ActiveWorkbook.Sheets.Add(, after:=ActiveSheet)
sh.Name = x(k)
d.items()(k).Copy sh.Range("a" & 2)
Rows("1:1").Copy sh.Range("a1")
sh.Cells.EntireColumn.AutoFit
Next
For Each sh In Worksheets
If sh.Name <> "Sheet1" Then
m = m + 1
If m = 1 Then sh.Select Else sh.Select False
End If
Next
ActiveWindow.SelectedSheets.Move
ActiveWorkbook.Close True, ThisWorkbook.Path & "\拆分后的表"
Sheets("Sheet1").Select
Application.ScreenUpdating = True
tim2 = Timer
MsgBox Format(tim2 - tim1, "拆分完成,共耗时:0.00秒"), 64, "时间统计"
End Sub |
评分
-
1
查看全部评分
-
|