|
本帖最后由 opel-wong 于 2020-3-11 08:03 编辑
如果在总表最前插入一行标题,
修改一下就可以了,代码如下:
- Sub test()
- Dim ws As Worksheet, R%, i%, arr, c%, aa
- Dim d As Object: Set d = CreateObject("scripting.dictionary")
- Dim d1 As Object: Set d1 = CreateObject("scripting.dictionary")
- For Each ws In Worksheets
- d1(ws.Name) = ""
- Next
- With Worksheets("总表")
- R = .Cells(.Rows.Count, 1).End(xlUp).Row
- c = .Cells(2, .Columns.Count).End(xlToLeft).Column
- arr = .Range("b1:b" & R)
- For i = 3 To UBound(arr)
- If Not d.Exists(arr(i, 1)) Then
- Set d(arr(i, 1)) = .Range("a2").Resize(1, c)
- End If
- Set d(arr(i, 1)) = Union(d(arr(i, 1)), .Cells(i, 1).Resize(1, c))
- Next
- End With
- For Each aa In d.Keys
- If Not d1.Exists(aa) Then
- Set ws = Worksheets.Add(after:=Worksheets(Worksheets.Count))
- End If
- With Worksheets(aa)
- .Cells.Clear
- d(aa).Copy .Range("a1")
- End With
- Next
- End Sub
复制代码
|
|