VBA 宏打印循环

Question

[下面更新]

我一直在尝试为我的生产表编写打印宏。

除了实际的打印输出之外，一切都很好。如果我使用 .Zoom = False 而不是 .Zoom = 50，则打印区域在打印输出表上会变得很小。如果我使用 Zoom=50，我会在左侧和右侧获得这些英寸宽的边距。我怀疑它以某种方式不处理实际的 printarea 行，但我不知道为什么，因为其他命令行似乎工作得很好。我尝试将代码精简为几乎 printarea、fitTopagesxx，但遇到了同样的问题。

我现在多次尝试重写代码，要么收到错误提示，要么得到与网络上找到的其他代码相同的结果。

   Sub PrintJob()
        Dim ws As Worksheet
        Dim i As Long

    Set ws = Sheets("Filtered_List")

        For i = 2 To ws.Cells(Rows.Count, "F").End(xlUp).Row
        If ws.Cells(i, "F").Value = 0 Then Exit For
            With Sheets("Print_Page")
                .Range("C8").Value = ws.Cells(i, "F").Value
                Worksheets("Print_Page").PageSetup.PrintArea = "$C$2:$L$60"
                Worksheets("Print_Page").PageSetup.Orientation = xlPortrait
                Worksheets("Print_Page").PageSetup.Zoom = 50
                Worksheets("Print_Page").PageSetup.FitToPagesWide = 1
                Worksheets("Print_Page").PageSetup.FitToPagesTall = False
                Worksheets("Print_Page").PageSetup.LeftMargin = Application.InchesToPoints(0)
                Worksheets("Print_Page").PageSetup.RightMargin = Application.InchesToPoints(0)
                Worksheets("Print_Page").PageSetup.TopMargin = Application.InchesToPoints(0)
                Worksheets("Print_Page").PageSetup.BottomMargin = Application.InchesToPoints(0)
                Worksheets("Print_Page").PageSetup.HeaderMargin = Application.InchesToPoints(0)
                Worksheets("Print_Page").PageSetup.FooterMargin = Application.InchesToPoints(0)
                .PrintOut
            End With
    Next i
End Sub

[更新:]在发现这是一个工作表特定错误后，我在此处提供了一些帮助后解决了问题。基本上，打印标题字段需要为空，执行此操作的代码如下:

.PrintTitleRows = ""
.PrintTitleColumns = ""

我又添加了几行，并使用了 Noldor130884 中清理后的代码:

Sub PrintJob()
    Dim ws As Worksheet
    Dim i As Long

    Set ws = Sheets("Filtered_List")

        For i = 2 To ws.Cells(Rows.Count, "F").End(xlUp).Row
        If ws.Cells(i, "F").Value = 0 Then Exit For
            With Worksheets("Print_Page")
            .Range("C8").Value = ws.Cells(i, "F").Value
                With .PageSetup
                .PrintArea = "$C$2:$L$60"
                .Orientation = xlPortrait
                .Zoom = False
                .FitToPagesWide = 1
                .FitToPagesTall = False
                .LeftMargin = Application.InchesToPoints(0)
                .RightMargin = Application.InchesToPoints(0)
                .TopMargin = Application.InchesToPoints(0)
                .BottomMargin = Application.InchesToPoints(0)
                .HeaderMargin = Application.InchesToPoints(0)
                .FooterMargin = Application.InchesToPoints(0)
                .PrintTitleRows = ""
                .PrintTitleColumns = ""
                .LeftHeader = ""
                .CenterHeader = ""
                .RightHeader = ""
                .LeftFooter = ""
                .CenterFooter = ""
                .RightFooter = ""
                .LeftMargin = Application.InchesToPoints(0)
                .RightMargin = Application.InchesToPoints(0)
                .TopMargin = Application.InchesToPoints(0)
                .BottomMargin = Application.InchesToPoints(0)
                .HeaderMargin = Application.InchesToPoints(0)
                .FooterMargin = Application.InchesToPoints(0)
                .PrintHeadings = False
                .CenterHorizontally = True
                .CenterVertically = False
                .PaperSize = xlPaperLetter

                End With
                .PrintPreview
        End With
    Next i
End Sub 

希望这能让某人免于一些头痛。

Answer 1

首先，让我稍微纠正一下您的代码:

With Worksheets("Print_Page")
    .Range("C8").Value = ws.Cells(i, "F").Value
    With .PageSetup
        .PrintArea = "$C$2:$L$60"
        .Orientation = xlPortrait
        .Zoom = 50
        .FitToPagesWide = 1
        .FitToPagesTall = False
        .LeftMargin = Application.InchesToPoints(0)
        .RightMargin = Application.InchesToPoints(0)
        .TopMargin = Application.InchesToPoints(0)
        .BottomMargin = Application.InchesToPoints(0)
        .HeaderMargin = Application.InchesToPoints(0)
        .FooterMargin = Application.InchesToPoints(0)
    End With
    .PrintOut
End With

现在，请注意as Microsoft says , Zoom = False 表示“FitToPagesWide 和 FitToPagesTall 属性控制工作表的缩放方式。”。

在您的代码中，您在这两个属性之前使用了 Zoom，因此您正在覆盖。

如果我正确理解了您想要执行的操作，请从您的代码中删除:

.FitToPagesWide = 1
.FitToPagesTall = False