Help with VBA code printing multiple worksheets to a single pdf file

Hello,

I am a complete newbie with VBA, just started "working" with it a couple of days ago... I have created an excel 2010 file for component information, each component has 3 different sheets to log different data. In total there are 30 components to the assembly so 90 sheets. Depending on 2 selection criteria I want to print the generated data (by the selection) as a single pdf file. After looking around I used the VBA code as below to generate a control sheet in which my selection creteria generates a "X" behind the sheet name in order to print the sheet. Everything works as intended however this code generates multiple pdf files which can add up to 30 different files, not really usefull. I looked at the code as specified in the knowledge base for printing multiple worksheets to a single pdf file, but as a newbie I am a bit puzzled how to intigrate this. Could anybody help me with the integration so my output will be a signgle pdf file? It does not need to be hardwired in name or location, as it works right know is ok if the output would be one file. Thanks for any help!

Michael Spaan

Sub Controlsheet()
Dim i As Integer
On Error Resume Next 'Delete this sheet if it already exists
Sheets("Control Sheet").Delete
On Error GoTo 0
Sheets.Add 'Add the WhatToPrint Sheet
ActiveSheet.Name = "Control Sheet"
Range("A1").Select 'Label the columns
ActiveCell.FormulaR1C1 = "Sheet Name"
Range("B1").Select
ActiveCell.FormulaR1C1 = "Print?"
Cells.Select
Selection.Columns.AutoFit
For i = 1 To ActiveWorkbook.Sheets.Count
Cells(i + 1, 1).Value = Sheets(i).Name
Next
End Sub
Sub PrintSelectedSheets()
Dim i As Integer
i = 2
Do Until Sheets("Control Sheet").Cells(i, 1).Value = ""
If Trim(Sheets("Control Sheet").Cells(i, 2).Value <> "") Then
Sheets(Sheets("Control Sheet").Cells(i, 1).Value).Select
ActiveWindow.SelectedSheets.PrintOut Copies:=1, ActivePrinter:="PDFCreator", _
Collate:=True
End If
i = i + 1
Loop
End Sub

By double clicking on my overview sheet with the code below the pdf print job is started.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Call PrintSelectedSheets
End Sub

try (untested):