Solved: Printing multiple worksheets to a single pdf file

[andrewvanmar]

Hi all,

I got redirected by Ken Pulse to this site for a specific question I have. (excel 2003)

I found on his site an article on how to print multiple sheets to a singe pdf which is exactly what I need for a project i'm doing.
[I can't post links yet, so i'll do this the convoluted way:
go to www dot excelguru dot ca slash node slash 21 ]

But since I know next to nothing about vba, I can't understand the code well enough to alter it to work on my workbook.Attachment 5993

What I want to happen is the first 3 sheets ( by name "Information", "IPL Service" and "Signatures and pricing") to be printed in a single PDF file.

What I can't figure out is how to use the code:
where do I enter the sheet names it has to print, and how do I link it to a button (when I tried that i got an internal error).

can anyone help with this? (you'll have my eternal gratitude if that's any incentive )



[vba]
Option Explicit
Sub PrintToPDF_MultiSheetToOne_Early()
'Author : Ken Puls (link removed)
'Macro Purpose: Print to PDF file using PDFCreator
' (Download from link removed)
' Designed for early bind, set reference to PDFCreator
Dim pdfjob As PDFCreator.clsPDFCreator
Dim sPDFName As String
Dim sPDFPath As String
Dim lSheet As Long
Dim lTtlSheets As Long
'/// Change the output file name here! ///
sPDFName = "Consolidated.pdf"
sPDFPath = ActiveWorkbook.Path & Application.PathSeparator
Set pdfjob = New PDFCreator.clsPDFCreator
'Make sure the PDF printer can start
If pdfjob.cStart("/NoProcessingAtStartup") = False Then
MsgBox "Can't initialize PDFCreator.", vbCritical + _
vbOKOnly, "Error!"
Exit Sub
End If
'Set all defaults
With pdfjob
.cOption("UseAutosave") = 1
.cOption("UseAutosaveDirectory") = 1
.cOption("AutosaveDirectory") = sPDFPath
.cOption("AutosaveFilename") = sPDFName
.cOption("AutosaveFormat") = 0 ' 0 = PDF
.cClearCache
End With
'Print the document to PDF
lTtlSheets = Application.Sheets.Count
For lSheet = 1 To Application.Sheets.Count
On Error Resume Next 'To deal with chart sheets
If Not IsEmpty(Application.Sheets(lSheet).UsedRange) Then
Application.Sheets(lSheet).PrintOut copies:=1, ActivePrinter:="PDFCreator"
Else
lTtlSheets = lTtlSheets - 1
End If
On Error GoTo 0
Next lSheet
'Wait until all print jobs have entered the print queue
Do Until pdfjob.cCountOfPrintjobs = lTtlSheets
DoEvents
Loop
'Combine all PDFs into a single file and stop the printer
With pdfjob
.cCombineAll
.cPrinterStop = False
End With
'Wait until PDF creator is finished then release the objects
Do Until pdfjob.cCountOfPrintjobs = 0
DoEvents
Loop
pdfjob.cClose
Set pdfjob = Nothing
End Sub
[/vba]