Print 2 Excel sheets to same PDF File

[groundin]

Hi,
I need to print 2 excel sheets into the same PDF File (I use Primo PDF if that matters).
Currently the way it works is that it selects sheet then prints it, then call another macro and then selects that sheet and prints it.
But when it does that it is basically creating 2 separate PDF Files which I don't want.
Also I don't need to have the print dialog box come up twice.
Any Ideas?

Sub PrintDaily()
'
' PrintDaily Macro
'

'
    Sheets("Daily (1)").Select
    Sheets("Dont Unhide Daily Report").Visible = True
    Sheets("Dont Unhide Daily Report").Select
    Application.Dialogs(xlDialogPrinterSetup).Show
    ActiveWindow.SelectedSheets.PrintOut copies:=1, Collate:=True, _
        IgnorePrintAreas:=False
    Sheets("Dont Unhide Daily Report").Select
    ActiveWindow.SelectedSheets.Visible = False
    ActiveWindow.ScrollWorkbookTabs Position:=xlFirst
    Sheets("Daily (1)").Select
    Call PrintCosts
    Range("F4").Select
End Sub

Sub PrintCosts()
'
' PrintCosts Macro
'

'
    Sheets("Costs").Select
    Sheets("Don't Unhide Costs Report").Visible = True
    Sheets("Don't Unhide Costs Report").Select
     Application.Dialogs(xlDialogPrinterSetup).Show
     ActiveWindow.SelectedSheets.PrintOut copies:=1, Collate:=True, _
        IgnorePrintAreas:=False
    Sheets("Don't Unhide Costs Report").Select
    ActiveWindow.SelectedSheets.Visible = False
    Sheets("Costs").Select
    ActiveWindow.SmallScroll Down:=-6
    Range("D40").Select
End Sub

[6StringJazzer]

Try this, but I didn't create a file to test it. It would be easier to have yours.

Sub PrintBoth()
    
    Dim SheetNames() As String
    SheetNames = Array("Dont Unhide Daily Report", "Don't Unhide Costs Report")
    
    Sheets(SheetNames).Visible = True
    Sheets(SheetNames).Select
    
    Application.Dialogs(xlDialogPrinterSetup).Show
    ActiveWindow.SelectedSheets.PrintOut copies:=1, Collate:=True, _
        IgnorePrintAreas:=False
        
    Sheets(SheetNames).Visible = False
    
    Sheets("Costs").Select
    ActiveWindow.SmallScroll Down:=-6
    Sheets("Costs").Range("D40").Select
    
End Sub

[groundin]

Hi 6StringJazzer,
Thanks! I seem to be getting an error in

SheetNames = Array("Dont Unhide Daily Report", "Don't Unhide Costs Report")

line when I try it.
My file is to large to upload

[6StringJazzer]

OK, this one was tested in a mock-up.

Sub PrintBoth()
    
    Dim SheetNames As Variant
    SheetNames = Array("Dont Unhide Daily Report", "Don't Unhide Costs Report")
    
    Sheets("Dont Unhide Daily Report").Visible = True
    Sheets("Don't Unhide Costs Report").Visible = True
    
    Sheets(SheetNames).Select
    
    Application.Dialogs(xlDialogPrinterSetup).Show
    ActiveWindow.SelectedSheets.PrintOut copies:=1, Collate:=True, _
        IgnorePrintAreas:=False
        
    Sheets("Dont Unhide Daily Report").Visible = False
    Sheets("Don't Unhide Costs Report").Visible = False
    
    Sheets("Costs").Select
    ActiveWindow.SmallScroll Down:=-6
    Sheets("Costs").Range("D40").Select
    
End Sub

[6StringJazzer]

Modify the code as shown. You will have to specify the location where you want it to be saved as shown. If you want to allow the user to specify anything they want then maybe the original version is your best bet.

Sub PrintBoth()
    
    Dim SheetNames As Variant
    SheetNames = Array("Dont Unhide Daily Report", "Don't Unhide Costs Report")
    
    Sheets("Dont Unhide Daily Report").Visible = True
    Sheets("Don't Unhide Costs Report").Visible = True
    
    Sheets(SheetNames).Select
    
    ActiveSheet.ExportAsFixedFormat _
      Type:=xlTypePDF, _
      Filename:="YOUR FILE PATH GOES HERE" & ".pdf", _
      Quality:=xlQualityStandard, _
      IncludeDocProperties:=True, _
      IgnorePrintAreas:=False, _
      OpenAfterPublish:=False

        
    Sheets("Dont Unhide Daily Report").Visible = False
    Sheets("Don't Unhide Costs Report").Visible = False
    
    Sheets("Costs").Select
    ActiveWindow.SmallScroll Down:=-6
    Sheets("Costs").Range("D40").Select
    
End Sub

[groundin]

Thanks, Sorry about late reply, but works good!

[6StringJazzer]

This could also be exported directly to PDF natively in 2007+ without having to bring up the print dialog for the user and going through a third-party PDF writer. Do you need to go through the print dialog, or would you prefer to go direct to PDF?

[groundin]

Sure, what would that look like, may be easier...