Double sided print (same page on reverse)

[dziw]

I have an Excel workbook which has two sheets. The first sheet i
several pages long when printed and the second sheet is always one pag
long. I want to print the second sheet on the reverse side of ever
printed page.

For example:

Printed sheet 1 front = Excel sheet 1, page 1
Printed sheet 1 reverse = Excel sheet 2

Printed sheet 2 front = Excel sheet 1, page 2
Printed sheet 2 reverse = Excel sheet 2

Printed sheet 3 front = Excel sheet 1, page 3
Printed sheet 3 reverse = Excel sheet 2

.....etc etc

The printer I use is capable of duplex printing but I cannot figure ou
how to print Excel sheet 2 on the reverse of every page. Does this nee
a macro

 

[Gord Dibben]

Might be just as easy to print single-sided sheet1 pages first.

Then flip over and print x number of copies of sheet2.


Gord Dibben MS Excel MVP

 

[dziw]

Thank you for your reply Gord. At the moment I do what you suggested an
flip the sheets but this can be time consuming, plus the printe
sometimes jams when loaded with pre-printed paper. I have in the pas
batch printed sheet2 but then had to make a change which resulted i
waste.

Ideally I would like a script that would print the pages from sheet1 o
one side and print sheet2 on the reverse as per my example below
Unfortunately I'm not a programmer and wouldn't know where to start!

I'm sure it must be possible to do this Does anyone hav
any code, if so it would be very much appreciated :

 

[dziw]

What I have so far is...

Sub printout()
Sheets("sheet1").Select
ActiveWindow.SelectedSheets.printout From:=1, To:=1, Copies:=1
Sheets("sheet2").Select
ActiveWindow.SelectedSheets.printout From:=1, To:=1, Copies:=1
Sheets("sheet1").Select
ActiveWindow.SelectedSheets.printout From:=2, To:=2, Copies:=1
Sheets("sheet2").Select
ActiveWindow.SelectedSheets.printout From:=1, To:=1, Copies:=1
Sheets("sheet1").Select
ActiveWindow.SelectedSheets.printout From:=3, To:=3, Copies:=1
Sheets("sheet2").Select
ActiveWindow.SelectedSheets.printout From:=1, To:=1, Copies:=1
End Sub

But this results in individual sheets being sent to the printer so i
will not print on both sides of the page even though it is set t
duplex.

Also I have to manually edit the code if there are more or less page
on sheet1. It would be handy if the code could check the number o
pages itself

 

[dziw]

Another possible solution may be to output the pages as .ps files,
convert them to PDF and merge them using Ghostscript?

But again I don't know how to get a the script to recoginise the number
of pages on sheet1 and output them appropriately. What I have so far
is...

Sub printout()
Sheets("sheet1").Select
ActiveWindow.SelectedSheets.printout From:=1, To:=1, Copies:=1 ,
PrToFileName:="1.ps"
Sheets("sheet2").Select
ActiveWindow.SelectedSheets.printout From:=1, To:=1, Copies:=1,
PrToFileName:="2.ps"
Sheets("sheet1").Select
ActiveWindow.SelectedSheets.printout From:=2, To:=2, Copies:=1,
PrToFileName:="3.ps"
Sheets("sheet2").Select
ActiveWindow.SelectedSheets.printout From:=1, To:=1, Copies:=1,
PrToFileName:="4.ps"
Sheets("sheet1").Select
ActiveWindow.SelectedSheets.printout From:=3, To:=3, Copies:=1,
PrToFileName:="5.ps"
Sheets("sheet2").Select
ActiveWindow.SelectedSheets.printout From:=1, To:=1, Copies:=1,
PrToFileName:="6.ps"
End Sub

But this won't work if there is more or less that 6 pages on sheet1

 

[Gord Dibben]

My printer is not capable of duplex printing so I cannot experiment.

My duplexing consists of manually flipping paper.

Hang n there. Possibly someone can come up with a solution.


Gord

Thank you for your reply Gord. At the moment I do what you suggested and
flip the sheets but this can be time consuming, plus the printer
sometimes jams when loaded with pre-printed paper. I have in the past
batch printed sheet2 but then had to make a change which resulted in
waste.

Ideally I would like a script that would print the pages from sheet1 on
one side and print sheet2 on the reverse as per my example below.
Unfortunately I'm not a programmer and wouldn't know where to start!

I'm sure it must be possible to do this Does anyone have
any code, if so it would be very much appreciated

Gord Dibben MS Excel MVP

 

[dziw]

OK, thanks Gord.

In my experience if no one replies within the first day of a forum post
then you don't normally get a reply but I will check back now and then
and see if anyone has a solution to this.

Maybe there isn't a solution to this?

 

[dziw]

Found the code below elsewhere on forum and was wondering if this coul
be modified to do what I require?


Code
-------------------

Sub Print_Odd()
Dim Totalpage As Long
Dim page As Long
Totalpage = Application.ExecuteExcel4Macro("GET.DOCUMENT(50)")
For page = 1 To Totalpage Step 2 'Odd
ActiveSheet.PrintOut from:=page, To:=page, _
Copies:=1, Collate:=True
Next
End Sub