3

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.

Nica
  • 177
  • 4
  • 17
  • What if you simply omit `MsgBox`? BTW are you sure `Copies:=PageTo` is correct? – AcsErno Jun 28 '18 at 07:10
  • I tried to do what you said but it prints in separate pages. I want to print these in 1 page only, duplex printing. – Nica Jun 28 '18 at 07:16
  • 1
    The only way is to combine the sheets into 1 print command like `Sheets(Array("For Print", "Sheet4")).PrintOut` but this will be tricky with `From:=PageFrom, To:=PageTo` then. With 2 distinct `.PrintOut` commands there is no chance to get them on one peace of paper. – Pᴇʜ Jun 28 '18 at 07:31
  • I'm sorry PEH but your suggestion did not work as well. The code does not print the 2nd sheet, "Sheet4" even I did not include From:=PageFrom, To:=PageTo – Nica Jun 28 '18 at 07:43
  • 1
    @Nica Well, I use exactly this code to print different sheets on one peace of paper (duplex). You must have done something wrong. – Pᴇʜ Jun 28 '18 at 08:13
  • @Pᴇʜ I will check. Thank you for your help. – Nica Jun 28 '18 at 09:00

1 Answers1

5

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

Maciej Los
  • 8,468
  • 1
  • 20
  • 35
  • Thank you but what I need is 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. – Nica Jul 06 '18 at 03:30
  • @Nica, You're very welcome. You wrote that you need to print sheets without changing printer settings. On the other hand you want to control printer via API. Both statements contradict each other! – Maciej Los Jul 06 '18 at 06:35
  • Take a look here: [Excel VBA printer API, set colour and duplex](https://stackoverflow.com/questions/40848751/excel-vba-printer-api-set-colour-and-duplex) and [Controlling the Printer in a Macro](https://excel.tips.net/T002530_Controlling_the_Printer_in_a_Macro.html) and [Working With Multiple Printers](https://excel.tips.net/T002217_Working_With_Multiple_Printers.html) – Maciej Los Jul 06 '18 at 06:42
  • Thank you @maciej los i will these. – Nica Jul 09 '18 at 06:02
  • Please edit you answer @maciej los so I can upvote it. Also please upvote my question afterwards. – Nica Jul 10 '18 at 05:43
  • OK, i updated my answer by adding a set of links from my previous comment. Upvoted your question too. Cheers, Maciej – Maciej Los Jul 10 '18 at 06:22