VBA代码设置打印区域，适合1x1页面并且不为某些选项卡设置打印区域

Question

我有这段代码来设置工作簿中某些工作表的打印区域。我正在尝试设置打印区域，使其适合一页上的每个选项卡，并且不会将其分为几页。另外，有人可以帮我编写代码，以便不根据每个选项卡中的相同单元格值在某些选项卡上设置打印区域吗？

Sub PrintArea()

    Dim I As Integer
    Dim ws As Worksheet

    WS_Count = ActiveWorkbook.Worksheets.Count
    For I = 1 To 6

    Set ws = ThisWorkbook.Sheets(I)

    ws.PageSetup.PrintArea = ws.Range("A1:Z24").Address

    With ws.PageSetup
        .Orientation = xlLandscape
        .FitToPagesWide = 1
        .FitToPagesTall = 1

   End With
   Next I

End Sub

Answer 1

你就快到了。您可以直接循环工作表，而不必使用计数器引用它们。

这应该可以很好地设置你:)

Sub PrintArea()

Application.ScreenUpdating = False

Dim ws As Worksheet

For Each ws In ActiveWorkbook.Worksheets

    Select Case ws.Index

        Case Is = 1, 2, 3, 4, 5, 6 'only perform action on first 6 sheets

            If ws.Range("A25") = "Yes" Then 'adjust to desired range and indicator

                With ws.PageSetup
                    .PrintArea = ws.Range("A1:Z24").Address
                    .Orientation = xlLandscape
                    .FitToPagesWide = 1
                    .FitToPagesTall = 1
                End With

                'set print area to only 1 page
                With ws
                    .Activate 'need to activate sheet to view Page Break Preview
                    ActiveWindow.View = xlPageBreakPreview 'need to view Page Break Preview to drag page breaks
                    .VPageBreaks(1).DragOff Direction:=xlToRight, RegionIndex:=1 `drag page over so it's just one page
                    ActiveWindow.View = xlNormalView 'set view back to normal
                End With

            End If

    End Select

Next ws

End Sub