Export multiple Excel Sheets to PDF

[sassy]

Hi, I am trying to export multiple sheets in a workbook to one single PDF. there are sheets in the workbook that i do not want exported. i have the code to export the single active sheet, but can't seem to find the code to work to export multiple sheets without sending the entire workbook. Also, I get the name of the file from one of the sheets and don't want to loose that ability, if possible.

this is what i have...

Private Sub ExportToPDF()
Sheets("QA Sheet 1").Select
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:="M:\Data\ Team Files\Audit Database\Audits\" & ActiveSheet.Range("E1") & " MARP.pdf", _
Quality:=xlQualityStandard, IncludeDocProperties:=False, _
IgnorePrintAreas:=True, OpenAfterPublish:=True
End Sub
Can anyone help!!!!

 

[mjrofra]

Hi,

maybe you can create a temporary copy with the sheets that you want to export as PDF and then export that workbook, somethint like this:

Sub Test()

    
    Dim tempExcelFile As String
    Dim PDF_FileName As String
    
            
    PDF_FileName = "M:\Data\ Team Files\Audit Database\Audits\" & ActiveSheet.Range("E1")

    '  Sheets to export as PDF
    Sheets(Array("Sheet1", "Sheet3", "Sheet5")).Copy
    
    
    With ActiveWorkbook
    
        .Save
        
        tempExcelFile = .FullName

        .ExportAsFixedFormat Type:=xlTypePDF, _
                             Filename:=PDF_FileName & " MARP.pdf", _
                             Quality:=xlQualityStandard, IncludeDocProperties:=False, _
                             IgnorePrintAreas:=True, _
                             OpenAfterPublish:=True
                             
        .Close
        
    End With

    Kill tempExcelFile
         

End Sub

 

[sassy]

Great, this works!!! thanks for your help!!

 

[samerickson89]

Looks like I'm almost a decade late to this party, but I just finished putting together a sub that exports all visible sheets to a single PDF so I thought I'd share it here in case others come looking for something similar. There's also some code in there to make sure the page setup is uniform. Should be simple enough to alter the page setup to your specific needs, and select whatever sheets you need as opposed to all visible sheets like I have.

Sub Print_Report()

Dim FileName As String
'code to define file name (mine was based on cells within the worksheet)

Dim ws As Worksheet
For Each ws In Sheets
     'make page setup uniform
     With ws.PageSetup
          .Orientation = xlPortrait
         .PrintArea = "$A$1:$Q$33"
         .Zoom = False
         .FitToPagesTall = False
         .FitToPagesWide = 1
     End With
     'select all visible sheets, don't replace selection
     If ws.Visible Then ws.Select (False)
Next

'output as .pdf with file name defined above
ActiveSheet.ExportAsFixedFormat _
     Type:=xlTypePDF, _
     FileName:=ActiveWorkbook.Path & "\Completed Inspection Reports" & OutName, _
     Quality:=xlQualityStandard, _
     IncludeDocProperties:=False, _
     IgnorePrintAreas:=False, _
     OpenAfterPublish:=True

End Sub

I'm still a little confused as to why 'ActiveSheet.Export...' prints all the selected sheets, but 'Selection.Export...' doesn't. Does selecting those sheets make them all active?