Excel can create a pdf with multiple worksheets. The key is to put the worksheet tab names into an array. Order of the worksheets in the array does not matter. Worksheets are put into the pdf by their worksheet index order. When I want to do a specific order, I make the macro create a scratch workbook with worksheets in the order for the pdf.
Simple example:
'http://www.vbaexpress.com/forum/showthread.php?63325-Combine-2-sheets-into-1-PDF-file
Sub Main()
Dim s$
Worksheets(Array("PrintCustomer", "Items")).Select
s = PublishToPDF( _
ThisWorkbook.Path, _
ActiveSheet, _
True)
'Open file, remove or comment next line. This is just to show that it worked.
Shell "cmd /c " & """" & s & """", vbNormalFocus
Worksheets(1).Select
End Sub
Function PublishToPDF(fName As String, o As Object, _
Optional tfGetFilename As Boolean = False) As String
Dim rc As Variant
rc = fName
If tfGetFilename Then
rc = Application.GetSaveAsFilename(fName, "PDF (*.pdf), *.pdf", 1, "Publish to PDF")
If rc = "" Then Exit Function
End If
o.ExportAsFixedFormat Type:=xlTypePDF, Filename:=rc _
, Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas _
:=False, OpenAfterPublish:=False
PublishToPDF = rc
End Function