Solved

Excel print multiple worksheets as one document

Posted on 2011-09-19
7
357 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 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
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

 
LVL 50

Assisted Solution

by:teylyn
teylyn 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:teylyn
teylyn 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

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

Approximate matching with VLOOKUP and MATCH seems to me to be a greatly under-used technique, and one which is vital for getting good performance out of large lookups. Until recently I would always have advised using an exact match for simplicity an…
Convert between Excel file formats (.XLS, .XLSX, .XLSM) with/without macro option David Miller (dlmille) Intro Over this past Fall, I've had the opportunity to see several similar requests and have developed a couple related solutions associate…
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…

757 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now