Excel print multiple worksheets as one document

Hi,

I'm trying to write a macro that exports my excel document to pdf. I can do this one worksheet at a time but i wish to export specific worksheets (dependent on a table of contents) all into the one pdf file.

For example, say i have worksheets A - F, and this time round i want A,B,D and E all to be exported to the one pdf file.

Thanks
WTC_ServicesAsked:
Who is Participating?
 
GirardAndrewConnect With a Mentor Commented:
Set Print Areas for each sheet, then Save As... PDF

Options > Publish what > Entire workbook
0
 
WTC_ServicesAuthor Commented:
Girard,

Thanks this is a good start, the problem is that i wish to print only specific pages. I see now that if I highlight specific pages and choose "Selection" it only prints those pages.

How can I write a macro with an if statement that checks my contents page to determine which sheets to save? It's really the if and select statements that i dont know how to write.
0
 
WTC_ServicesAuthor Commented:
With my last comment, I am able to highlight a specific area on a worksheet, but this still doesnt allow me to save multiple worksheets as pdf
0
The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

 
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Connect With a Mentor Microsoft MVP ExcelCommented:
Hello,

manually, I would Ctl-click all the sheets, then print to a PDF printer. Recorded by the macro recorder, the selection bit looks like this:

    Sheets(Array("Sheet1", "Sheet3", "Sheet4")).Select
    Sheets("Sheet1").Activate
    ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True, _
        IgnorePrintAreas:=False

Open in new window


Is that something you could use?

cheers, teylyn
0
 
WTC_ServicesAuthor Commented:
teylyn,

your method allowed me to print, however when I tried to save as pdf, this method didn't work, maybe my syntax was wrong?

Sheets(Array("Sheet1", "Sheet2")).Select
    Sheets("Sheet1").Activate
    ActiveWindow.SelectedSheets.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
        "C:\Users\nickf\Desktop\experts_saveas.pdf", Quality:=xlQualityStandard, _
        IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:= _
        True
End Sub

What I need to know is how to write an if statement in the macro, so that for example if(A1="YES",Print page 1, Don't print page 1).

Could I possibly hide worksheets rather than select, then print the entire workbook?
0
 
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Connect With a Mentor Microsoft MVP ExcelCommented:
Hello,

saving as PDF does not work that way, printing to a PDF printer like Adobe Distiller or PDFWriter or some such, would. But with a "save as" command you cannot single out individual sheets.

If you want to save as PDF, the steps would be different, i.e. select the sheets, copy them to a new file, save the new file as a PDF, close the new file. This code does that. You may want to use an input dialog to let the user specify a file name.

Dim fName As String, fPath As String
'
fName = InputBox("Enter a name for the PDF, including the PDF extension")
fPath = "C:\Dump\" & fName

    Sheets(Array("Sheet1", "Sheet3")).Select
    Sheets("Sheet3").Activate
    Sheets(Array("Sheet1", "Sheet3")).Copy
    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
        fPath, Quality:= _
        xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, _
        OpenAfterPublish:=False
    Application.DisplayAlerts = False
    ActiveWindow.Close
    Application.DisplayAlerts = True

Open in new window

0
 
WTC_ServicesAuthor Commented:
Thanks,

Using a combination of all three answers i've managed to write a macro that hides unnecessary sheets, then prints the whole worksheet and unhides the worksheets.

WTC
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.