Solved

Excel print multiple worksheets as one document

Posted on 2011-09-19
7
362 Views
Last Modified: 2012-05-12
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
0
Comment
Question by:WTC_Services
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 2
7 Comments
 
LVL 5

Accepted Solution

by:
GirardAndrew earned 167 total points
ID: 36564794
Set Print Areas for each sheet, then Save As... PDF

Options > Publish what > Entire workbook
0
 

Author Comment

by:WTC_Services
ID: 36564824
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
 

Author Comment

by:WTC_Services
ID: 36564839
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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 50

Assisted Solution

by:Ingeborg Hawighorst
Ingeborg Hawighorst earned 333 total points
ID: 36564843
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
 

Author Comment

by:WTC_Services
ID: 36564897
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
 
LVL 50

Assisted Solution

by:Ingeborg Hawighorst
Ingeborg Hawighorst earned 333 total points
ID: 36564965
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
 

Author Closing Comment

by:WTC_Services
ID: 36584713
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

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

749 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question