|
发表于 2016-12-13 14:37
|
显示全部楼层
本楼为最佳答案
- Sub 生成报销表()
- Set d = CreateObject("scripting.dictionary")
- Set d1 = CreateObject("scripting.dictionary")
-
- arr = Sheets(2).[a1].CurrentRegion
- For i = 2 To UBound(arr)
- xm = arr(i, 2) & arr(i, 3) & arr(i, 4) '姓名
- d(xm) = i
- Next
-
- brr = Sheets(1).[a1].CurrentRegion
- For i = 2 To UBound(brr)
- yy = brr(i, 7) '原因
- xm = brr(i, 2) & brr(i, 3) & brr(i, 4)
- If d.exists(xm) Then d1(yy) = d1(yy) & "," & d(xm)
- Next
-
- Call 删除
- For Each yy In d1.keys
- Sheets(2).Copy after:=Sheets(Sheets.Count)
- With ActiveSheet
- .Name = yy & "-报销表"
- .[a2:r1000].ClearContents
- .[a2:r1000].Borders.LineStyle = 0
- xrr = Split(d1(yy), ",")
- ReDim crr(1 To UBound(xrr), 1 To UBound(arr, 2))
- For i = 1 To UBound(xrr)
- For j = 1 To UBound(arr, 2)
- crr(i, j) = arr(xrr(i), j)
- Next
- Next
- .[a2].Resize(i - 1, UBound(arr, 2)) = crr
- .[a2].Resize(i - 1, UBound(arr, 2)).Borders.LineStyle = 1
- End With
- Next
- Sheets(1).Activate
- End Sub
- Sub 删除()
- Application.DisplayAlerts = False
- For Each sh In Worksheets
- If sh.Index > 2 Then sh.Delete
- Next
- Application.DisplayAlerts = True
- End Sub
复制代码 |
评分
-
查看全部评分
|