How to fix code to save all worksheets as one PDF? VBA

user_18619922 0
Viewed 38

'How to fix code to save all worksheets as one PDF from the open workbook? VBA. It only prints the active worksheet right now for some reason.

Sub abc()

For i = 1 To Workbook.Worksheets.Count
With ActiveSheet.PageSetup
    .CenterHeader = "Sample Excel File Saved As PDF"
    .Orientation = xlPortrait
    .PrintArea = "$B$5:$F$105"
    .PrintTitleRows = ActiveSheet.Rows(5).Address
    .Zoom = False
    .FitToPagesTall = False
    .FitToPagesWide = 1
End With
 
ActiveSheet.ExportAsFixedFormat _
    Type:=xlTypePDF, _
    Filename:="Sample Excel File Saved As PDF 2", _
    Quality:=xlQualityStandard, _
    IncludeDocProperties:=False, _
    IgnorePrintAreas:=False, _
    From:=1, _
    To:=5, _
    OpenAfterPublish:=True
ActiveSheet.Next.Activate
Next i

End Sub

worksheet worksheet-function vba excel pdf
1 Answers

I did this very same thing just a few days ago. Try it like this.

Worksheets(Array("Sheet1", "Sheet2")).Select

ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
    "C:\Consolidated.pdf", Quality:= xlQualityStandard, IncludeDocProperties:=True, _
     IgnorePrintAreas:=False, OpenAfterPublish:=True

Or, if you want specific ranges, do it like this.

one = Worksheets("Sheet1").Range("A1:B10")
two = Worksheets("Sheet2").Range("A1:B10")
three = Worksheets("Sheet3").Range("A1:B10")

    Worksheets(Array(one, two, three)).Select
    
    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
        "C:\Consolidated.pdf", Quality:= xlQualityStandard, IncludeDocProperties:=True, _
         IgnorePrintAreas:=False, OpenAfterPublish:=True

one = ""
two = ""
three = ""
user_52126140
Related