?
Solved

Excel print multiple worksheets as one document

Posted on 2011-09-19
7
Medium Priority
?
370 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
  • 4
  • 2
7 Comments
 
LVL 5

Accepted Solution

by:
GirardAndrew earned 668 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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 50

Assisted Solution

by:Ingeborg Hawighorst (Microsoft MVP / EE MVE)
Ingeborg Hawighorst (Microsoft MVP / EE MVE) earned 1332 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 1332 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

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

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

Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

850 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