|
代码放“表1”工作表代码区,按钮用activex按钮- Private Sub CommandButton1_Click()
- tim1 = Timer
- Application.DisplayAlerts = False
- For Each sht In Sheets
- If sht.Name <> "表1" Then sht.Delete
- Next
- Application.DisplayAlerts = True
- Set d = CreateObject("scripting.dictionary")
- arr = [a1].CurrentRegion
- For i = 1 To UBound(arr)
- For j = 1 To UBound(arr, 2)
- s = Left(arr(i, 3), 1)
- If Not d.exists(s) Then
- Set d(s) = Range("a" & i).Resize(1, j)
- Else
- Set d(s) = Union(d(s), Range("a" & i).Resize(1, j))
- End If
- Next
- Next
- x = d.keys
- For k = 1 To UBound(x)
- Set sht = ActiveWorkbook.Sheets.Add(, after:=ActiveSheet)
- sht.Name = x(k) & "部门"
- d.items()(k).Copy sht.[a2]
- Rows("1").Copy sht.[a1]
- Next
- tim2 = Timer
- MsgBox Format(tim2 - tim1, "拆分完成,共耗时:0.00秒"), 64, "时间统计"
- End Sub
复制代码
|
|