I have an Excel workbook which has two sheets. The first sheet is
several pages long when printed and the second sheet is always one page
long. I want to print the second sheet on the reverse side of every
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 out
how to print Excel sheet 2 on the reverse of every page. Does this need
a macro?


--
dziw
------------------------------------------------------------------------
dziw's Profile: http://www.excelforum.com/member.php...o&userid=10422
View this thread: http://www.excelforum.com/showthread...hreadid=562818

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

On Wed, 19 Jul 2006 09:02:14 -0400, dziw
wrote:


I have an Excel workbook which has two sheets. The first sheet is
several pages long when printed and the second sheet is always one page
long. I want to print the second sheet on the reverse side of every
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 out
how to print Excel sheet 2 on the reverse of every page. Does this need
a macro?

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 :)


--
dziw
------------------------------------------------------------------------
dziw's Profile: http://www.excelforum.com/member.php...o&userid=10422
View this thread: http://www.excelforum.com/showthread...hreadid=562818

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 it
will not print on both sides of the page even though it is set to
duplex.

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


--
dziw
------------------------------------------------------------------------
dziw's Profile: http://www.excelforum.com/member.php...o&userid=10422
View this thread: http://www.excelforum.com/showthread...hreadid=562818

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


--
dziw
------------------------------------------------------------------------
dziw's Profile: http://www.excelforum.com/member.php...o&userid=10422
View this thread: http://www.excelforum.com/showthread...hreadid=562818

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

On Wed, 19 Jul 2006 16:56:55 -0400, dziw
wrote:


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

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
------------------------------------------------------------------------
dziw's Profile: http://www.excelforum.com/member.php...o&userid=10422
View this thread: http://www.excelforum.com/showthread...hreadid=562818

Found the code below elsewhere on forum and was wondering if this could
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

--------------------


--
dziw
------------------------------------------------------------------------
dziw's Profile: http://www.excelforum.com/member.php...o&userid=10422
View this thread: http://www.excelforum.com/showthread...hreadid=562818