Excel VBA to Export Selected Sheets to PDF

Question

I m using the following code to export selected sheets from Excel 2010 to a single pdf file     ThisWorkbook Sheets Array  Sheet1    Sheet2    Sheet3    Select  ActiveSheet ExportAsFixedFormat           Type  xlTypePDF            Filename   C  temp pdf             Quality  xlQualityStandard            IncludeDocProperties  True            IgnorePrintAreas  False            OpenAfterPublish  True   My problem is that it only exports the first sheet   Any ideas

User · Answer

Once you have Selected a group of sheets, you can use Selection

Consider:

Sub luxation()
    ThisWorkbook.Sheets(Array("Sheet1", "Sheet2", "Sheet3")).Select
    Selection.ExportAsFixedFormat _
        Type:=xlTypePDF, _
        Filename:="C:\TestFolder\temp.pdf", _
        Quality:=xlQualityStandard, _
        IncludeDocProperties:=True, _
        IgnorePrintAreas:=False, _
        OpenAfterPublish:=True
End Sub

EDIT#1:

Further testing has reveled that this technique depends on the group of cells selected on each worksheet. To get a comprehensive output, use something like:

Sub Macro1()

   Sheets("Sheet1").Activate
   ActiveSheet.UsedRange.Select
   Sheets("Sheet2").Activate
   ActiveSheet.UsedRange.Select
   Sheets("Sheet3").Activate
   ActiveSheet.UsedRange.Select

   ThisWorkbook.Sheets(Array("Sheet1", "Sheet2", "Sheet3")).Select
   Selection.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
      "C:\Users\James\Desktop\pdfmaker.pdf", Quality:=xlQualityStandard, _
      IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:= _
      True
End Sub

User · Answer

I m pretty mixed up on this  I am also running Excel 2010  I tried saving two sheets as a single PDF using       ThisWorkbook Sheets Array 1 2   Select       Selection   ExportAsFixedFormat xlTypePDF  FileName  amp    pdf       False   but I got nothing but blank pages  It saved both sheets  but nothing on them  It wasn t until I used       ThisWorkbook Sheets Array 1 2   Select       ActiveSheet   ExportAsFixedFormat xlTypePDF  FileName  amp    pdf       False   that I got a single PDF file with both sheets    I tried manually saving these two pages using Selection in the Options dialog to save the two sheets I had selected  but got blank pages  When I tried the Active Sheet s  option  I got what I wanted  When I recorded this as a macro  Excel used ActiveSheet when it successfully published the PDF  What gives

User · Answer

this is what i came up with as i was having issues with  asp8811 answer maybe my own difficulties     this will do the put the first 2 sheets in a pdf       Note each ws should be controlled with page breaks for printing which is a bit fiddly       this will explicitly put the pdf in the current dir  Sub luxation2       Dim Filename As String     Filename    temp201     Dim shtAry   ReDim shtAry 1    this is an array of length 2 For i   1 To 2 shtAry i - 1    Sheets i  Name Debug Print Sheets i  Name Next i Sheets shtAry  Select Debug Print ThisWorkbook Path  amp            ActiveSheet ExportAsFixedFormat xlTypePDF  ThisWorkbook Path  amp       amp  Filename  amp    pdf       False  End Sub

[excel] Excel VBA to Export Selected Sheets to PDF

Examples related to excel

Examples related to vba

Examples related to pdf

Examples related to export