CODEWITHSUNDEEP
vbaexcel
zipzit
zipzit

Reputation: 3997

How can I print multiple Excel (XL) sheets to my full duplex printer? (Two sided Printouts)

I've gotta problem. I've got a large XL workbook that generates two output 'pages' that I use for daily planning. One output page has three month's calendars, things to do lists, and daily schedule. The second output page has a very convenient way to keep track of daily notes and design sketches. Unfortunately the workbook has four worksheets, two worksheets for managing content and two for formatting output. Oh, and for reference, the worksheet started as a Vertex42 document.. I've done lots of updates to the workbook, particularly with the addition of a page 2.

Its absolutely critical that the output sheets end up on both sides of a single piece of paper.

The normal XL print controls allow for three basic options: you can print 1)a selection area, or 2) a single sheet or 3) an entire workbook be printed. I know I can print the output sheets using a macro individually, but that only uses one side of the paper on my duplex printer. How can I print out two of four worksheets within a workbook so the pages end up on both sides of a single piece of paper?

My current macro:
Sub PrintMultipleDays()

Dim theStartDate As Date
Dim theEndDate As Date
Dim i, d As Integer
Dim daysToPrint As Integer

theStartDate = Range("theDate").Value
theEndDate = Range("endDate").Value 
daysToPrint = theEndDate - theStartDate

Dim msg As String
msg = "Print " & daysToPrint + 1 & " Days?" & Chr(10) & theStartDate & " To " & theEndDate
If (MsgBox(msg, vbYesNo, "Print Multiple Days?") = vbYes) Then
    ' continue
Else
    Exit Sub
End If

For i = 0 To daysToPrint Step 1
    Range("theDate").Value = theStartDate + i
    ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
    Sheets("Page2").Select
    ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True, _
        IgnorePrintAreas:=False
    Sheets("Page1").Select
Next i

'Reset
Range("theDate").Value = theStartDate

End Sub

Note: generally I like to print about four pages at a time (that's 8 output pages, four pieces of paper...) I've been using a pdf printer tool and appending the output files to generate a single (8 page long) pdf file, but that tool is no longer working. I need to find a better solution. Any ideas on mods to this macro??

Or do I just abandon the whole XL thing, do this in html5 canvas or SVG or with a JavaScriptPDF library and do it online for all to share in the goodness? (How many hours to do that? ugh.) I will say, jsPDF seems like a pretty awesome tool, but it would be a ton of work to reproduce some of this functionality.

enter image description here enter image description here

Upvotes: 0

Views: 3696

Answers (1)

MP24
MP24

Reputation: 3200

Basically, you can select two or more sheets by clicking the mouse while holding down CTRL (or shift, if sheets are consecutive). Then you can print all of them together in one print job. This also works in VBA by using an Array as parameter to the Sheets collection:

Sheets(Array("sheet1", "sheet2"). PrintOut Copies:=1, Collate:=True

Upvotes: 1

Related Questions