Excel VBA to Save Selected Sheets in Combination as PDFs

Question

I have a workbook with many worksheets. I would like to save as two-page PDFs, where the first page is Worksheet 1, and the second page is Worksheets 2-x. My code currently only allows me to save individual PDFs for each worksheet in the workbook. I am wondering what to add to it to make it do this. Can anyone share some advice?

Thanks!

Option Explicit

Sub createPDFfiles()
Dim ws As Worksheet
Dim Fname As String
For Each ws In ActiveWorkbook.Worksheets
    On Error Resume Next

    Fname = "C:\Folder\" & ws.Name & "Report" & Format(Date, "yyyy-mm-dd") & ".pdf"

   ws.ExportAsFixedFormat _
        Type:=xlTypePDF, _
        Filename:=Fname, _
        Quality:=xlQualityStandard, _
        IncludeDocProperties:=True, _
        IgnorePrintAreas:=False
Next ws
End Sub

Did you mean that you want the first sheet in one file and the rest of the sheets in a second file? — mechanical_meat, Nov 07 '14 at 22:48
To clarify, I'm looking for multiple two-page PDFs, where Worksheet 1 is basically a cover page on all the PDFs. i.e., (Worksheet1 + Worksheet2), (Worksheet1 + Worksheet3), (Worksheet1 + Worksheet4), etc. When I first wrote this code, I didn't need the cover sheet so it was fine to export all the sheets as individual PDFs. — Andrew, Nov 07 '14 at 22:51
http://stackoverflow.com/questions/14404650/excel-save-multiple-sheets-to-pdf/14407986#14407986 — Tim Williams, Nov 08 '14 at 05:24
Thanks Tim - will I have to enter each sheet combination into the code manually? The number of worksheets in the workbook, but I always need worksheet 1. — Andrew, Nov 10 '14 at 13:41
Sorry, it cut me off. In `ThisWorkbook.Sheets(Array("Sheet1", "Sheet2")).Select`, Sheet1 can stay the same in every output, but Sheet2 in the output will be different in each instance. Is there a way to write Sheet2 so it changes to the subsequent sheets in each output? My output combinations will be Sheet1 + Sheet2, Sheet1 + Sheet3, Sheet1 + Sheet4, etc. for all the worksheets in the workbook. Thanks! — Andrew, Nov 10 '14 at 13:53

score 0 · Answer 1 · answered Nov 07 '14 at 22:43

0

You are enumerating through the worksheets and doing your save inside that loop. That is why you are getting one PDF per worksheet. Try using just workbook instead of ActiveWorkbook.Worksheets.

answered Nov 07 '14 at 22:43

gwhenning

138
1
3
14

Thanks for the response - to clarify, I'm looking for multiple two-page PDFs. i.e., (Worksheet1 + Worksheet2), (Worksheet1 + Worksheet3), (Worksheet1 + Worksheet4), etc. – Andrew Nov 07 '14 at 22:47
OK, super quick nudge in the general direction, try selecting worksheet 1 + current worksheet within your loop and then do the export. I can't remember off the top of my head, but it will probably be something like `with selected.worksheets.. Select Worksheet1...` – gwhenning Nov 07 '14 at 23:21

score 0 · Answer 2 · answered Nov 17 '14 at 16:52

Gah. It was staring me in the face the whole time. I amended the code to include a selection, and named the second worksheet ws.Name. Final script looks like this:

Option Explicit

    Sub createPDFfiles()
    Dim ws As Worksheet
    Dim Fname As String
    For Each ws In ActiveWorkbook.Worksheets
        On Error Resume Next

        Fname = "C:\Folder\" & ws.Name & "Report" &   Format(Date, "yyyy-mm-dd") & ".pdf"

        Sheets(Array("Sheet1", ws.Name)).Select

    ActiveSheet.ExportAsFixedFormat _
            Type:=xlTypePDF, _
            Filename:=Fname, _
            Quality:=xlQualityStandard, _
            IncludeDocProperties:=True, _
            IgnorePrintAreas:=False
    Next ws
End Sub

Thanks for your help everyone!

Excel VBA to Save Selected Sheets in Combination as PDFs

2 Answers2