Saving multiple selected worksheets as a single PDF

Question

I am trying to save multiple selected worksheets into the one single PDF.

relativePath = wb.Path & "\" & sheetName & dateToday

wb.Sheets(Array("Sheet1", "Sheet2", "Sheet3")).Select

***This is the code in question***.ExportAsFixedFormat Type:=xlTypePDF, _
Filename:=relativePath, _
Quality:=xlQualityStandard, IncludeDocProperties:=True, _
IgnorePrintAreas:=False, OpenAfterPublish:=True

I have tried two methods in modifying "This is the code in question"

I have used 'Selection'
- Result: A blank document is saved as a PDF with nothing in it
I have used 'ActveSheet'
- Result: A document is saved as a PDF with only the first sheet
I have used 'wb' (this is the my current workbook, just to test if it works)
- Result: As expected a document is saved as a PDF with ALL sheets inside, this was mainly to test to see if the PDF functionality was working

To clarify, my desired result is sheet1, sheet2 and sheet3 to be saved into the same PDF (i have a bunch of other sheets in my workbook and I will replace the statically coded sheet names with an array when i get it working)

I have gotten both solution 1 and 2 from stackoverflow answers, but both do not work for me where they work for other people.

Can anyone please shed some light on a solution OR at least identify why these two things are happening?

If you want, you can make it as a UserForm with a ListBox control to display the sheets, and then from there select the sheets that you want to export as a single PDF file. Take a look at this: stackoverflow.com/questions/33914691/… — Vulthil
Sorry, maybe I should have said in my question. I'm iterating through multiple workbooks and worksheets and want to save as PDF for selected worksheets in each workbook and i'd like to automate it so no interaction is required. — drcoding

Eager Einstein Eager Einstein · Accepted Answer · 2015-11-27T04:17:41

@drcoding

I have worked on the code submitted above by you. It seems instead of the line

wb.Sheets(Array("Sheet1", "Sheet2", "Sheet3")).Select

you could use;

Worksheets("Sheet1").Select (False)
Worksheets("Sheet2").Select (False)
Worksheets("Sheet3").Select (False)

the Sheet1,2,3 can be replaced by the name you later decide to give to the sheet. Also do note that use (false) so that the Sheet 1 is not deselected once Sheet 2 is selected.

I had made similar code which I am posting below, you too can use it. It loops through multiple sheet name in a sheet 'File Index' and is able to convert all the sheets selected 'Y' in Column I in that sheet. It also uses a similar file name like you have selected in relativePath = wb.Path & "\" & sheetName & dateToday. Do try this out if you please;

Link: http://profit21.blogspot.in/2015/05/template-for-entity-financial-reporting.html

Link to file: https://www.dropbox.com/s/hi4wfxqz9ixiuby/Profit21.blogspot.in%20Limited%20-%20FY%202014-15%20-%20Accounts%20%26%20Reports%20%282015%201_54%29.xltm?dl=0

The code is as below:

Sub TEFAR_Convert_to_PDF()

Dim Count As Integer
Dim SheetsToPDF As Integer

    Application.ScreenUpdating = False
    Sheets("File Index").Select
    Cells(7, 9).Select

For Count = 7 To 207

    'If Worksheets("File Index").Cells(Count, 9) = "" Then GoTo outofi:
        If Worksheets("File Index").Cells(Count, 9) = "Y" Then    '1
            sheetnam = Worksheets("File Index").Cells(Count, 7)
            If IsError(sheetnam) = True Then sheetnam = ""
            If sheetnam <> "" Then                              '2
                Worksheets(sheetnam).Select (False)
            End If                                              '2
        End If                                                  '1

Next Count

outofi:



PDF_filename = (Application.ActiveWorkbook.Path & "\" & Worksheets("Entity Master Data").Cells(3, 3) & " - " & Worksheets("Entity Master Data").Cells(13, 3) & " - " & "PDF Copy of Annual Report.pdf")
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=PDF_filename, Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False

MsgBox ("File has been saved as PDF in " & PDF_filename & " .")

Sheets("File Index").Select
Application.ScreenUpdating = True


End Sub

Cheers! EE

Saving multiple selected worksheets as a single PDF

1 Answers