|
[广告] VBA代码宝 - VBA编程加强工具 · VBA代码随查随用 · 内置多项VBA编程加强工具 ★ 免费下载 ★ ★ 使用手册★
本帖最后由 MOY838840554 于 2018-12-1 14:21 编辑
把关闭屏幕刷新的代码删了,顺便把不能全部打印的修改好了
Sub 自动打印()
Dim i%, j, k%, arr, n$, m%
Dim 页数%, 描述$, 输入$, 打印页数, df$
页数 = 1: 描述 = "": df = "全部打印"
k = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
ActiveSheet.ResetAllPageBreaks
On Error GoTo 跳过:
'**********插入页码并获取页数
For i = 5 To k
If Cells(i, 1) <> Cells(i - 1, 1) Then
ActiveSheet.HPageBreaks.Add Before:=Cells(i, 1)
页数 = 页数 + 1
End If
Next
'**********获取类别,并存入数组arr,生成描述
ReDim arr(1 To 页数)
j = 1
arr(j) = Cells(4, 1)
描述 = 描述 & "【" & j & "】" & arr(j) & ";"
For i = 5 To k
If Cells(i, 1) <> Cells(i - 1, 1) Then
j = j + 1
arr(j) = Cells(i, 1)
If j Mod 6 = 0 Then 描述 = 描述 & Chr(10)
描述 = 描述 & "【" & j & "】" & arr(j) & ";"
End If
Next
'**********输入打印页码
re:
m = 0
输入 = InputBox("输入打印范围: " & Chr(10) & "(1):【2-7】打印2至7页" & Chr(10) _
& "(2):【1.3.4.6.8.9】打印指定页(升序)" & Chr(10) _
& "【可打印总页数】:" & 页数 & "页" & Chr(10) & 描述, "打印范围", df)
If 输入 = "" Then
MsgBox "取消打印", , "提示"
Exit Sub
ElseIf 输入 <> "全部打印" And IsNumeric(Replace(Replace(输入, ".", ""), "-", "")) = False Then '初步验证输入的内容
MsgBox "输入非数字字符", , "提示"
df = 输入
GoTo re:
End If
'**********处理输入的文本
If 输入 = "全部打印" Then '全部打印
ReDim 打印页数(0 To 页数 - 1)
For i = 0 To 页数 - 1
打印页数(m) = i
m = m + 1
Next
ElseIf InStr(输入, "-") > 0 Then 'N-M页
n = InStr(输入, "-")
If Replace(输入, Left(输入, n), "") > j Or IsNumeric(Replace(输入, Left(输入, n), "")) = False Then
MsgBox "输入错误,或【输入页数】不可大于【最大页数】", , "注意"
df = 输入
GoTo re:
End If
ReDim 打印页数(0 To Replace(输入, Left(输入, n), "") - Left(输入, n - 1))
For i = Left(输入, n - 1) * 1 To Replace(输入, Left(输入, n), "") * 1
打印页数(m) = i
m = m + 1
Next
Else '打印指定页
打印页数 = Split(输入, ".")
If 打印页数(UBound(打印页数)) * 1 > j Then
MsgBox "【输入页数】不可大于【最大页数】", , "注意"
df = 输入
GoTo re:
End If
End If
'**********一页一页打印
For i = 1 To UBound(打印页数) + 1
Cells(2, 2) = arr(i) '改变B2值
ActiveWindow.SelectedSheets.PrintOut From:=i, To:=打印页数(i - 1), Copies:=打印页数(i - 1), Collate _
:=True, IgnorePrintAreas:=False
Next i
MsgBox "打印完毕", , "提示"
跳过:
End Sub
|
|