Printing multiple sheets at once, plus some options

[mharper90]

I have a macro below that I've used to try to print multiple sheets in a single print job. There are 11 sheets that I do not want printed, and all of the others need to be printed. The remaining sheets that are to be printed are all formatted with the same print area, each is 2 pages landscape, and they need to be able to be duplex printed.

The macro below populates a single print preview screen with all of the sheets I desire (all sheets minus the 11 specifically called out). The preview screen shows all of the right pages as a single job. I then set up the printer settings for auto-duplex and click print, but in the bottom corner of the screen, 2 different "print job sent to the printer" windows appear. And when I get to the printer, the first sheet has printed auto-duplex, but the remaining sheets print single sided only. I only click print once, and the preview screen makes it appear as if all of the sheets are a single job. I'm really confused as to why the job gets split up like that and therefore doesn't retain the duplex settings for all pages.

Lastly, once that's fixed, I would like to have an option to use the same sheet range (all sheets minus the 11 specifically called out), but offer an option to print just the front pages of each sheet (each sheet is a front and back), and then another option of print just the back pages of each sheet. This is a macro for a program with a large user base, so I want to offer options for users to print double-sided without a duplexing printer.

Here's my stab at the code. Hopefully someone can help me figure out my flaw...

Sub PrintERCs()

Dim ws     As Worksheet
Dim msgValue
     msgValue = MsgBox("ARE YOU SURE YOU WANT TO PRINT ALL ERCS?", vbYesNo + vbCritical, "PRINT WARNING!")

     If msgValue = vbYes Then
          msgValue = MsgBox("Ensure:" & vbNewLine & "     - LANDSCAPE" & vbNewLine & "     - 2-SIDED/DUPLEX PRINTING" & vbNewLine & "are set under PAGE SETUP ""OPTIONS"" before clicking ""PRINT""!", vbOKOnly, "Proper Printing")
     Sheets(11).Select
     For Each ws In ThisWorkbook.Worksheets
          If ws.Name <> "Main Data" And ws.Name <> "START" And ws.Name <> "Master Blank for ERC" And ws.Name <> "P1 Figure 2-2" And ws.Name <> "P2 Figure 2-2" And ws.Name <> "P3 Figure 2-2" And ws.Name <> "P4 Figure 2-2" And ws.Name <> "P5 Figure 2-2" And ws.Name <> "P6 Figure 2-2" And ws.Name <> "P7 Figure 2-2" And ws.Name <> "P8 Figure 2-2" Then
               If ws.Visible = xlSheetVisible Then
                    ws.Select False
               End If
          End If
     Next ws
     Application.Dialogs(xlDialogPrint).Show
     ElseIf msgValue = vbNO Then
     End If

Sheets("Main Data").Activate
Sheets("Main Data").Select
Sheets("Main Data").Range("A2").Select

End Sub

 

[DSCfromCFA]

G'day m,

I'm not sure if this response will be any help, but for the second time today I have looked at a forum question that referenced a topic I was looking at here:
http://www.cpearson.com/Excel/PrintMultipleSheets.aspx

I do not have sufficient vba skills to know whether the article/code may give you an answer, the author mentions the single vs multiple print job issue and says his code avoids that but doens't seem to point out just what bit of code consolidates the multiple pages into a single print job, although if I was a betting man I would suggest the last line or two of actual code other than the End statements.

FWIW

shane