Solved

Excel print multiple worksheets as one document

Posted on 2011-09-19
7
364 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
Independent Software Vendors: 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 (Microsoft MVP / EE MVE)
Ingeborg Hawighorst (Microsoft MVP / EE MVE) 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 (Microsoft MVP / EE MVE)
Ingeborg Hawighorst (Microsoft MVP / EE MVE) 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

Independent Software Vendors: 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!

Question has a verified solution.

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

Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
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…
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

717 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