|
本帖最后由 chart888 于 2017-5-13 15:28 编辑
- Private Sub CommandButton1_Click()
- On Error Resume Next
- Application.ScreenUpdating = False
- Application.DisplayAlerts = False
- Dim Arr, Rng As Range, Sht As Worksheet, Dic As Object
- Dim k, t, Str As String, i As Long, lc As Long
- Arr = ThisWorkbook.Worksheets("总表").Range("A3").CurrentRegion.Value
- lc = UBound(Arr, 2)
- Set Rng = Rows(2)
- Set Dic = CreateObject("Scripting.Dictionary")
- For i = 3 To UBound(Arr)
- Str = Arr(i, 5)
- If Not Dic.Exists(Str) Then
- Set Dic(Str) = Cells(i, 1).Resize(, lc)
- Else
- Set Dic(Str) = Union(Dic(Str), Cells(i, 1).Resize(, lc))
- End If
- Next
- k = Dic.Keys
- t = Dic.Items
- With Sheets
- For i = 0 To Dic.Count - 1
- Set Sht = .Item(k(i))
- If Sht Is Nothing Then
- .Add(After:=.Item(.Count)).Name = k(i)
- Set Sht = ActiveSheet
- Else
- Sht.Cells.Clear
- End If
- Rng.Copy Sht.Range("A1")
- t(i).Copy Sht.Range("A2")
- Sht.Cells.EntireColumn.AutoFit
- Set Sht = Nothing
- Next
- End With
- Sheets(1).Activate
- Application.ScreenUpdating = True
- Application.DisplayAlerts = True
- End Sub
复制代码
|
|