Excel VBA Printing

[Stephen041085]

Hi,

 

I have a macro set up in Excel which prints certain worksheets from a workbook. This works fine, however all the sheets print as separate print jobs. I would like to find a way to change the code so that only one print job is created. The sheets need to be printed in the order of the code below, which is not the same order as the tabs in the worksheet. Changing the order of the tabs is not an option.

 

Any help appreciated!

 

Current Code:

 

Sub WorkSheetWithFoam()
   
  'Select the printer to print to
  If (Application.Dialogs(xlDialogPrinterSetup).Show) = True Then

  On Error Resume Next
   Sheets("Drawing").Select
   Sheets("Foam").PrintOut
   Sheets("Fitting").PrintOut
   Sheets("Extr.").PrintOut
   Sheets("Exterior").PrintOut
   Sheets("Timber").PrintOut
   Sheets("Timber").PrintOut
   Sheets("Drawing").PrintOut
   Sheets("Card").PrintOut
   Sheets("Summary").PrintOut
   Sheets("Input").PrintOut
   
   Else
   End If

End Sub

[LeightonJames]

Not sure if this is actually possible but:

 

could you not copy the sheets in the order you want them to print to a temporary workbook, then print the temporary workbook instead?

[mac_shinobi]

Print Multiple Worksheets As 1 Print Job

 

Sheets(Array("Drawing", "Foam", "Fitting", "Extr", "Exterior", "Timber", "Drawing", "Card", "Input", "Summary")).PrintOut 

 

Something like this ?

[Stephen041085]

mac_shinobi - I had seen that link and tried that. It looks like it should work, however I get 4 print jobs, and not in the right order.