Partial printing of entire worksheet (1st or 2nd pages only)

[Guest]

WinXP Pro SP2
Excel 2003 SP2

Excel 2003 printing allows for printing an entire workbook - all tabs and
all pages. I'd like to print ONLY the 1st and 2nd page of each tab
(worksheet) and not ALL pages that a tab/worksheet needs. How can I do this
via Excel 2003 SP2?
Selecting EACH tab/worksheet is too burdensome due to 50+ tabs/worksheets.
TIA!

 

[Gord Dibben]

Sub Print_Parts()
Dim ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets
ws.PrintOut From:=1, To:=2, Copies:=1, Preview _
:=False, Collate:=True
Next ws
End Sub

Tested with PrintPreview=True on a 10 sheet workbook.


Gord Dibben MS Excel MVP

 

[Guest]

Gord,
This IS ABSOLUTELY amazing! Worked perfectly!
Is it possible to have the printer consider the ENTIRE output as
one-stream/one-job rather than 'restarting' each time?

How did you learn this and how can I get started in this area? I've got a
development background but I've never looked at creating Excel macros. I
really don't know where to start and how they would help - except in VERY
limited areas.

Is there a website that has some goodies on Excel macros?

THANKS!
Tom

 

[Gord Dibben]

My VBA skills are not up to making one print job from several print jobs.

Hopefully someone will jump in and give a hand with making the pages from each
sheet into some kind of array that will print in one job.

Ron de Bruin has a site that provides many VBA print tips but nothing jumps out
at me.

http://www.rondebruin.nl/print.htm

Macros can help in many areas, not just "limited areas"

A good place to start on VBA and macros is David McRitchie's "getting started"
site.

http://www.mvps.org/dmcritchie/excel/getstarted.htm

And hang around these news groups for tips and snippets of code you can use.


Gord

 

[Dave Peterson]

I think you have a couple of choices...

Print to a .prn file (not a text file with .prn extension).
Record a macro when you click the "print to file" checkbox on the File|Print
dialog.

Then you could shell to DOS to copy all those .prn files to the printer as one
print job.

copy /b a1.prn + a2.prn + a3.prn lpt1

==
Another option would be to create a new worksheet and copy pages 1 and 2 from
each worksheet and paste them into that new worksheet and print from there.

Neither of these sound particularly appetizing to me.