|
- Sub tt()
- Dim wb As Workbook, brr()
- Dim CopyRng As Range
- Set CopyRng = Sheets("样表").Range("a1:h31") '要复制的表式
- xstr = "一二三四五六七八九" '表式中班级为阿拉伯数字型,据此转换成中文数字
- Set d = CreateObject("scripting.dictionary")
- Set d1 = CreateObject("scripting.dictionary")
- Set wb = Workbooks.Open(ThisWorkbook.Path & "\学生信息.xls")
- arr = wb.Worksheets(1).[a1].CurrentRegion
- wb.Close False
- For i = 2 To UBound(arr)
- x = arr(i, 2) & arr(i, 3)
- d(x) = d(x) & "," & arr(i, 1) '年级+班级为key,姓名累加为item
- y = arr(i, 2)
- d1(y) = "" '总共存在多少年级
- Next
- For Each y In d1.keys
- Worksheets.Add after:=Sheets(Sheets.Count) '新建年级表
- With ActiveSheet
- .Name = y
- For i = 1 To 9
- x = y & Mid(xstr, i, 1) & "班" '年级+班级为key
- If d.exists(x) Then
- CopyRng.Copy .Cells((i - 1) * 32 + 1, 1) '粘贴样表
- .Cells((i - 1) * 32 + 3, 1) = " 学校 " & Mid(y, 1, 1) & " 年级 " & i & " 班"
- xrr = Split(d(x), ",") '各姓名进数组xrr
- ReDim brr(1 To 25, 1 To 8) '显示数组
- For k = 1 To UBound(xrr)
- q = 2 * Int((k - 0.001) / 25) + 1
- p = k Mod 25: If p = 0 Then p = 25
- brr(p, q) = k
- brr(p, q + 1) = xrr(k)
- Next
- .Cells((i - 1) * 32 + 5, 1).Resize(25, 8) = brr
- .Cells((i - 1) * 32 + 30, 5) = k - 1 '本班人数
- End If
- Next
- End With
- Next
- End Sub
复制代码 |
|