Excel分页打印.docVIP

Excel分页打印 Attribute VB_Name = 分页打印 Sub FilesPrint() 激活环境后导入此文件，只打印，不更改表格设置 贵州省凯里路桥工程公司 杨克申 2011-6-13 On Error Resume Next Dim PAS1(1026), PAS2(1026) As Variant 定义分页行变量，以水平分页符总数1026为基数 With ActiveSheet.PageSetup 取消页面设置中的标题、页眉页脚 .PrintTitleRows = .PrintTitleColumns = .LeftHeader = .CenterHeader = .RightHeader = .LeftFooter = .CenterFooter = .RightFooter = End With Application.ScreenUpdating = True 开启屏幕更新 ActiveWindow.FreezePanes = False 取消单元格的拆分和冻结 Rows.Hidden = False 全部显示计算行高 ActiveSheet.ResetAllPageBreaks 重设分页符 RE = ActiveSheet.UsedRange.Rows.Count 最后一行数据行号 A1 = InputBox(顶标题行数：, , ): If A1 = Then Cells(1, 2).Select: End A2 = InputBox(底标题行数：, , ): If A2 = Then Cells(RE, 2).Select: End 底标题 行数总高 For MM = RE To RE - A2 + 1 Step -1 If Rows(MM).EntireRow.Hidden = False Then RH1 = RH1 + Rows(MM).RowHeight Next MM 顶标题 行数总高 For MM = 1 To A1 If Rows(MM).EntireRow.Hidden = False Then RH1 = RH1 + Rows(MM).RowHeight Next MM RCurrentCell = Range(ActiveSheet.HPageBreaks(1).Location.Address).Row 获得第一水平分页符（地址行号）位置 RL = RCurrentCell - 1 选择水平分页符上一行 For MM = 1 To RL If Rows(MM).EntireRow.Hidden = False Then RH3 = RH3 + Rows(MM).RowHeight 第一水平分页符以上未隐藏的所有行高总数，以此控制一页的范围 和 打印页数 Next MM 分页，循环计算 不含预设顶标题、底标题在内的所有行高 For MM = A1 + 1 To RE - A2 + 1 If Rows(MM).EntireRow.Hidden = False Then RH2 = RH2 + Rows(MM).RowHeight Next MM 计算总页数 If RH2 Mod (RH3 - RH1) = 0 Then PA = RH2 / (RH3 - RH1) Else PA = RH2 / (RH3 - RH1) + 1 End If NN = A1 + 1 打印或预览 MSG = MsgBox(Yes： 预览 vbCrLf No： 直接打印, vbYesNo, 打印) If MSG = vbYes Then 预览 For MPA = 1 To Int(PA) RH2 = 0 置0 For MM = NN To RE - A2 + 1 循环计算 不含预设顶标题、底标题在内的所有行高 If Rows(MM).EntireRow.Hidden = False Then RH2 = RH2 + Rows(MM).RowHeight If RH2 = (RH3 - RH1) Then Exit For 当累计行高总数达到一页总高时退出循环 Next MM ActiveSheet.PrintPreview 预览时页脚为空，但已留位置，点“直接打印”时有页脚 设置分页符位置 Set ActiveSheet.HPageBreaks(MPA).Location = ActiveSheet.Range(A MM) If NN = T