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?

Gary's Student · Answer 1 · 2015-12-15T18:13:38.710

30

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

edited Dec 15 '15 at 18:13

answered Dec 23 '13 at 20:59

Gary's Student

95,722
10
59
99

1

this only prints a blank sheet for me. not sure why? – HattrickNZ Jul 24 '14 at 22:56
1

Using Selection only seems to export a blank sheet in Excel 2013. The use of ActiveSheet as seen in ASP8811 and HattrickNZ's answers did work for us. – Adrian Jun 10 '15 at 10:08
you get a +1 for a good answer but also for the name of your sub. that's very clever. – Dexter Whelan Jan 30 '17 at 12:03
`Selection.ExportAsFixedFormat` only exports the selected cell or leads to empty PDF-Prints also in Office 365 (Office 2016). One has to export sheet by sheet as time being. – DrMarbuse Mar 20 '23 at 15:30

score 9 · Answer 2 · answered Apr 14 '14 at 16:49

9

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?

answered Apr 14 '14 at 16:49

asp8811

793
8
14

what is FileName equal to? can `FileName` in clude a path? [myref](http://msdn.microsoft.com/en-us/library/bb238907%28v=office.12%29.aspx) – HattrickNZ Jul 24 '14 at 23:35
I am now getting an error with this `Array(1,2)` what is this meant to be? its like it will only accept 1 worksheet name? – HattrickNZ Jul 24 '14 at 23:47
Sorry this is after the fact. I haven't logged on in awhile. Filename should include the file path to the location you want to save to as well as the file name. – asp8811 Sep 08 '14 at 13:46

score 4 · Answer 3 · answered Jul 25 '14 at 01:01

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 & "\"


    ActiveSheet.ExportAsFixedFormat xlTypePDF, ThisWorkbook.Path & "/" & Filename & ".pdf", , , False

End Sub

Mine only works with the first two sheets because that's what I was going for in my code. My answer wasn't really an answer, more of a clarification about using ActiveSheet instead of Selection because I didn't have the reputation to comment at the time. Your solution is much better for using multiple sheets. — asp8811, Sep 08 '14 at 13:55

Excel VBA to Export Selected Sheets to PDF

3 Answers3

Linked

Related