|
发表于 2016-3-23 09:15
|
显示全部楼层
本楼为最佳答案
- Sub 拆分()
- Dim bt1 As Range, bt2 As Range, rng As Range
- Set d = CreateObject("scripting.dictionary")
- Set d1 = CreateObject("scripting.dictionary")
- Set d2 = CreateObject("scripting.dictionary")
- With Sheet1
- Set bt1 = .[a1:ad1]
- arr = .[a1].CurrentRegion
- For i = 2 To 10 'UBound(arr)
- Set rng = .Cells(i, 1).Resize(1, 30)
- x = arr(i, 3)
- d(x) = ""
- If Not d1.exists(x) Then Set d1(x) = Union(bt1, rng) Else Set d1(x) = Union(d1(x), rng)
- Next
- End With
-
- With Sheet2
- Set bt2 = .[a1:q2]
- arr = .[a1].CurrentRegion
- For i = 3 To 11 'UBound(arr)
- Set rng = .Cells(i, 1).Resize(1, 17)
- x = arr(i, 3)
- d(x) = ""
- If Not d2.exists(x) Then Set d2(x) = Union(bt2, rng) Else Set d2(x) = Union(d2(x), rng)
- Next
- End With
-
- For Each x In d.keys
- k = 0
- Workbooks.Add
- With ActiveWorkbook
- If d1.exists(x) Then
- k = k + 1
- d1(x).Copy .Sheets(k).[a1]
- .Sheets(k).Name = "月薪"
- End If
- If d2.exists(x) Then
- k = k + 1
- d2(x).Copy .Sheets(k).[a1]
- .Sheets(k).Name = "补贴"
- End If
- .SaveAs ThisWorkbook.Path & "" & x & ".xls"
- .Close
- End With
- Next
- End Sub
复制代码 |
|