Excel VBA to Export Selected Sheets to PDF
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:=TrueEnd 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:= _ TrueEnd Sub
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 & ".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 & ".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?
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 2For i = 1 To 2shtAry(i - 1) = Sheets(i).NameDebug.Print Sheets(i).NameNext iSheets(shtAry).SelectDebug.Print ThisWorkbook.Path & "\" ActiveSheet.ExportAsFixedFormat xlTypePDF, ThisWorkbook.Path & "/" & Filename & ".pdf", , , FalseEnd Sub