VBA | Export To Multi-Page PDF-CodePudding

So I have a worksheet with a landscape orientation that I am exporting to a PDF. I can achieve this without any issues by selecting the usedRange of the activesheet, but the content prints to just one page, even if the used range is outside of the print area. How might I go about getting it to print a multi-page PDF in this case? My code is below:

 Dim numSheets As Integer
     numSheets = UBound(SlotArray)
    
     For z = LBound(SlotArray) To UBound(SlotArray)
         Set attendSh = ThisWorkbook.Sheets(SlotArray(z))
         attendSh.Activate
         ThisWorkbook.ActiveSheet.UsedRange.Select
     Next z
    
     Select Case numSheets
         Case "0"
             ThisWorkbook.Sheets(Array(SlotArray(0))).Select
         Case "1"
             ThisWorkbook.Sheets(Array(SlotArray(0), SlotArray(1))).Select
         Case "2"
             ThisWorkbook.Sheets(Array(SlotArray(0), SlotArray(1), SlotArray(2))).Select
         Case "3"
             ThisWorkbook.Sheets(Array(SlotArray(0), SlotArray(1), SlotArray(2), SlotArray(3))).Select
         Case "4"
             ThisWorkbook.Sheets(Array(SlotArray(0), SlotArray(1), SlotArray(2), SlotArray(3), SlotArray(4))).Select
         Case "5"
             ThisWorkbook.Sheets(Array(SlotArray(0), SlotArray(1), SlotArray(2), SlotArray(3), SlotArray(4), SlotArray(5))).Select
         Case "6"
             ThisWorkbook.Sheets(Array(SlotArray(0), SlotArray(1), SlotArray(2), SlotArray(3), SlotArray(4), SlotArray(5), SlotArray(6))).Select
     End Select

         Application.PrintCommunication = False
         With ThisWorkbook.ActiveSheet.PageSetup
             .Orientation = xlLandscape
             .CenterHorizontally = True
            '.Zoom = 90
             .FitToPagesWide = 1
             .FitToPagesTall = 1
             .PrintComments = False
             .PrintGridlines = False
         End With

         Application.PrintCommunication = True

         Selection.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
         FilePath & "\" & Year & " Monthly Attendance\" & "\" & Year & " " & Month & " Attendance.pdf", Quality:=xlQualityStandard, _
         IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:= _
         False

where slotArray is an array of sheet names.

CodePudding user response：

Try applying the PageSetUp to every sheet in the array.

    Dim numSheets As Long, Z As Long
    numSheets = UBound(SlotArray)
    
    Dim ws As Worksheet
    For Z = LBound(SlotArray) To UBound(SlotArray)
         Set ws = ThisWorkbook.Sheets(SlotArray(Z))
         With ws.PageSetup
             .Orientation = xlLandscape
             .CenterHorizontally = True
             .Zoom = False
             .FitToPagesWide = 1
             .FitToPagesTall = False
             .PrintComments = xlPrintNoComments
             .PrintGridlines = False
             .PrintArea = ws.UsedRange.Address
         End With
    Next Z
    ThisWorkbook.Sheets(SlotArray).Select
    
    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:="Attendance.pdf", Quality:=xlQualityStandard, _
    IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False
    
    MsgBox "Printed " & vbLf & Join(SlotArray, vbLf)