VBA code for Front to Back Print from 2 different Excel Worksheets

Question

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.

Answer 1

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