VBA个人所得税扣缴申报表一键排版

VBA个人所得税扣缴申报表一键排版

Sub 个人所得税扣缴申报表一键排版()

    With Sheets("个人所得税扣缴申报表")

        lastrow = .Cells(Rows.Count, 1).End(xlUp).Row

        Debug.Print lastrow, lastrow - 1, lastrow - 3

        ''设置A列AN列的列宽为6.75

        Columns("A:AN").ColumnWidth = 6.75

        ''先全体设置行高为13

        .Rows("8:" & lastrow).RowHeight = 13

        ''再设置第1行，第7行，倒数2，3行的行高为23

        .Range("1:1,7:7," & lastrow - 2 & ":" & lastrow - 1).RowHeight = 23

        ''选中A8到最后倒数4行的全部设置数据格式“字体”为缩小字体填充

        Range("A8:AN" & lastrow - 4).Select

        With Selection

            .HorizontalAlignment = xlCenter

            .VerticalAlignment = xlCenter

            .WrapText = False

            .Orientation = 0

            .AddIndent = False

            .IndentLevel = 0

            .ShrinkToFit = True

            .ReadingOrder = xlContext

        End With

        ''设置全体内容打印区域，页码，页面横向，A3纸

        With ActiveSheet.PageSetup

            .PrintTitleRows = "$5:$8"

            .PrintArea = "$A$1:$AN$" & lastrow

            .CenterFooter = "&P/&N"

            .LeftMargin = Application.InchesToPoints(0.6)

            .RightMargin = Application.InchesToPoints(0.6)

            .TopMargin = Application.InchesToPoints(0.6)

            .BottomMargin = Application.InchesToPoints(0.6)

            .HeaderMargin = Application.InchesToPoints(0.3)

            .FooterMargin = Application.InchesToPoints(0.3)

            .PrintHeadings = False

            .PrintGridlines = False

            .PrintComments = xlPrintNoComments

            .CenterHorizontally = True

            .CenterVertically = False

            .Orientation = xlLandscape

            .Draft = False

            .PaperSize = xlPaperA3

            .FirstPageNumber = xlAutomatic

            .Order = xlOverThenDown

            .BlackAndWhite = False

        End With

    End With

    ActiveWindow.View = xlPageBreakPreview

    ActiveSheet.VPageBreaks(1).DragOff Direction:=xlToRight, RegionIndex:=1

    'ActiveSheet.HPageBreaks(48).DragOff Direction:=xlDown, RegionIndex:=1

End Sub