VBA打印模式操作

http://pan.baidu.com/s/1qWoJxYs

Dim rCurrentCell As Range   ' 每一页之分页小计所在单元格
Dim r1stSubCell As Range    ' 小计区域第一个单元格

' -------------------------------------------------
' 从这里开始执行
Sub Main()
    Worksheets("工资表").Activate

    删除原有的分页小计行

    新建分页小计
End Sub
' -------------------------------------------------

Sub 删除原有的分页小计行()
    Set r1stSubCell = Range("A5")           ' 本例名单从 A5 单元格开始
    For Each rCurrentCell In Range(r1stSubCell, r1stSubCell.End(xlDown))
        If rCurrentCell = "本页小计" Then rCurrentCell.EntireRow.Delete
    Next
End Sub

Sub 新建分页小计()
    Dim iSubCol As Integer, rSubArea As Range
    Dim hb As HPageBreak

    ActiveWindow.View = xlPageBreakPreview  ' 进入 分页浏览 模式, 以便 EXCEL 正确计页
    Set r1stSubCell = Range("A5")           ' 本例名单从 A5 单元格开始
    iSubCol = 20                            ' 本例小计项共有 20 列

    ' 避免可能的错误:手工分页符正好与自动分页符重合
    ' 建议运行前先删除手工分页符
    ' 本过程可选
    'For Each hb In ActiveSheet.HPageBreaks
    '    On Error Resume Next
    '    If hb.Type = xlPageBreakManual Then hb.Delete
    'Next

    ' 最后一行插入手工分页符
    ActiveSheet.HPageBreaks.Add Before:=r1stSubCell.End(xlDown).Offset(1, 0)

    ' 测试每一个分页符,
    ' 如果是自动分页符, 则在其上一行插入一小计行, 而本行纳入下一页
    ' 否则, 在本行插入一小计行
    For Each hb In ActiveSheet.HPageBreaks
        Set rCurrentCell = hb.Location
        rCurrentCell.Select                 ' 看看先

        If hb.Type = xlPageBreakAutomatic Then Set rCurrentCell = rCurrentCell.Offset(-1, 0)

        rCurrentCell.EntireRow.Insert
        Set rCurrentCell = rCurrentCell.Offset(-1, 0)

        ' 添加分页小计内容
        With rCurrentCell
            .Value = "本页小计"
            .Font.Bold = True

            Set rSubArea = .Offset(0, 1).Resize(1, iSubCol) ' 需要填充分页小计公式的区域
            
            ' 使用 SUBTOTAL 公式的好处是方便扩展, 且不会对已计算区域重复计算(如果可能发生这种情况的话)
            rSubArea.Formula = "=SUBTOTAL(9," & r1stSubCell.Offset(0, 1).Address(1, 0) & ":" & .Offset(-1, 1).Address(1, 0) & ")"

            Set r1stSubCell = .Offset(1, 0)
        End With
    Next

    ActiveWindow.View = xlNormalView
End Sub

 

转载于:https://www.cnblogs.com/yuzhengdong/p/4176923.html