Printing multiple sheets at once, plus some options

mharper90

Board Regular
Joined
May 28, 2013
Messages
117
Office Version
  1. 365
Platform
  1. MacOS
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...


Code:
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
 

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.
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
 
Upvote 0

Forum statistics

Threads
1,213,504
Messages
6,114,020
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top