I have 2 Excel sheets namely "For Print" and "Sheet 4". I want to automatically print the sheets front to back using VBA. This is my current code but it requires user action after printing the 1st page.
Sub Rectangle4_Click()
Dim PageFrom As Integer
Dim PageTo As Integer
Dim xAnswer As Integer
PageFrom = Sheets("INPUT").Range("J2").Value
PageTo = Sheets("INPUT").Range("L2").Value
Sheets("For Print").PrintOut From:=PageFrom, To:=PageTo, Copies:=1,
Collate:=True
'--------------------------------------TO PRINT PAGE 2--------------------
xAnswer = MsgBox("Print Page 2?", vbYesNo + vbQuestion, "Empty Sheet")
If xAnswer = vbYes Then
Sheets("Sheet4").PrintOut From:=1, To:=1, Copies:=PageTo, Collate:=True
End If
End Sub
Please help :(
I need to print this without setting the printer properties. My boss told me to have a code which controls the printer's API. Our Printer is FujiXerox.
Thank you so much.
I tried to do what you said but it prints in separate pages. I want to print these in 1 page only, duplex printing.
Directly speaking a duplex priting is not an option (or a future) of Excel (or Excel's sheet). It's a feature of printer. So, depending of printer futures and printer settings, you may or you may not be able to print sheets on both sides of paper sheet.
Number of pages to print depends on sheet's PageSetup. For example, if you set sheets to print on 1 page:
With ThisWorkbook.Worksheets(Array("For Print", "Sheet4")).PageSetup
.Zoom = False
.FitToPagesTall = 1
.FitToPagesWide = 1
End With
and then you use PrintOut method, you'll see 2 pages. If duplex is available and has been properly set up, you'll get 1 page printed on both sides.
As PEH already mentioned, to be able to pass both sheets within single method is to use:
ThisWorkbook.Worksheets(Array("For Print", "Sheet4")).PrintOut
Please, follow the links i've provided in my answer for further details.
[EDIT]
Here is a set of links which may help you resolve your issue:
Excel VBA printer API, set colour and duplex
Controlling the Printer in a Macro
Working With Multiple Printers