PDA

View Full Version : Different print.



Tom Jones
06-30-2016, 05:35 AM
Howdy,

Is possible, using VBA code, to print double-sided on a sheet of paper, if what they want to print are in different sheets? : pray2:
The printer can do this.

Jan Karel Pieterse
06-30-2016, 06:04 AM
Depends on the layout of the sheets I guess. if it is one page per sheet the answer is yes. If there are more, it depends on how you'd want them to be printed. Excel will print all pages of a sheet and then all pages of the next so that is how they end up in the print. To make this easy, configure a new printer in Windows that by default prints double-sided and set the VBA to use that printer as you cannot set two-sided printing in Excel page setup and hence not in VBA.

Tom Jones
06-30-2016, 06:38 AM
Thanks for replay.

Manual can print on both sides of a sheet of paper but I have many different sheet and I would be easier to choose - using VBA code - which will be printed sheets.
Sheets 'fit' on page (I did this manual) but if you can help with VBA code to ask me, witch is the first sheet printed on the face of paper sheet, and the sheet to be printed on the back.

Thanks.

Jan Karel Pieterse
06-30-2016, 06:46 AM
This is not trivial. The sheets are printed in their order so the code should:
- Ask for each sheet
- arrange them in the order you have given
- print the group of sheets
AFter printing, must the worksheets be re-positioned in their original order too?

Tom Jones
06-30-2016, 07:48 AM
I do not want to abuse your kindness, but if the VBA code is complicated, then you no longer arrange the sheets. Remain as they are, after printing.
Thank you.

Jan Karel Pieterse
06-30-2016, 09:04 AM
This is one solution:


Sub Print2SheetsInOrder()
Dim oSh1 As Worksheet
Dim oSh2 As Worksheet
On Error Resume Next
Set oSh1 = Application.InputBox("Please select a cell on the first sheet to be printed", "Select cell on sheet", , , , , , 8).Parent
If Not oSh1 Is Nothing Then
Set oSh2 = Application.InputBox("Please select a cell on the second sheet to be printed", "Select cell on sheet", , , , , , 8).Parent
If Not oSh2 Is Nothing Then
oSh2.Move ThisWorkbook.Worksheets(1)
oSh1.Move ThisWorkbook.Worksheets(1)
Worksheets(Array(oSh1.Name, oSh2.Name)).PrintOut preview:=True
'remove grouping of sheets!
oSh1.Select
End If
End If
End Sub

Tom Jones
07-01-2016, 02:37 AM
Thank you so much.