|
[广告] VBA代码宝 - VBA编程加强工具 · VBA代码随查随用 · 内置多项VBA编程加强工具 ★ 免费下载 ★ ★ 使用手册★
Sub 生成()
Set d = CreateObject("scripting.dictionary")
arr = [a1].CurrentRegion
For i = 2 To UBound(arr)
zu = arr(i, 4)
If zu <> "" Then d(zu) = d(zu) & "-" & i
Next
Application.ScreenUpdating = False
rmax = 30 '每张表最大人数
For Each zu In d.keys
xrr = Split(d(zu), "-")
rs = UBound(xrr) '组的人数
n = (rs - 0.1) \ rmax + 1
For pg = 1 To n
Sheets(3).Copy after:=Sheets(Sheets.Count)
With ActiveSheet
.Name = zu & "-" & pg
ReDim brr(1 To rmax, 1 To UBound(arr, 2))
s = (pg - 1) * 30 '起始位置
For r = 1 To rmax
If r + s <= rs Then
i = xrr(r + s)
''' For j = 1 To UBound(arr, 2)
brr(r, 1) = arr(i, 1)
brr(r, 2) = arr(i, 3)
brr(r, 3) = arr(i, 4)
brr(r, 4) = arr(i, 6)
brr(r, 5) = arr(i, 7)
brr(r, 6) = arr(i, 9)
brr(r, 7) = arr(i, 11)
''' Next
' For j = 3 To 4 'UBound(arr, 2)
' brr(r, j) = arr(i, j)
' Next
End If
Next
.[B6].Resize(rmax, UBound(brr, 2)) = brr
End With
Next
Next
Sheets(1).Activate
Application.ScreenUpdating = True
End Sub
Sub 清除()
Application.ScreenUpdating = False
Application.DisplayAlerts = False
For Each sh In Worksheets
If sh.Index > 3 Then sh.Delete
Next
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub
Sub 打印()
For Each sh In Worksheets
If sh.Index > 2 Then sh.PrintPreview
'If sh.Index > 2 Then sh.PrintOut
Next
End Sub
|
|