This handles both chart sheets and worksheets in a workbook...
Sub PrintAllSheetsAtOnce()
Dim sht as Object 'as Sheet isn't available
For Each sht in Sheets
sht.PrintOut
Next
End Sub
'---

If you will only be printing worksheets then...
Sub PrintAllWorksheetsAtOnce()
Dim sht as Worksheet
For Each sht in Worksheets
sht.PrintOut
Next
End Sub
'---
Note that both Sheets and Worksheets refer to the active workbook.
Both of the above can be qualified with a reference to a particular workbook...
Workbooks("Sludge").Worksheets
Also, "PrintOut" has some optional arguments that are worth reviewing.
'---

I read some recent statistics that estimated 40% of Office users have made the switch to the Ribbon.
That still leaves 60% of us who haven't had to learn to drive on the wrong side of the road.
The commercial "Extras" add-in I offer, when installed on xl2007+, includes a classic menubar &
toolbars under the Add-ins tab.
It has made my life easier.
--
Jim Cone
Portland, Oregon USA .
http://www.mediafire.com/PrimitiveSoftware .
(free and commercial excel programs)




"StargateFan"
wrote in message
...
On Mon, 28 Mar 2011 19:18:17 -0700, "Jim Cone"
wrote:

Loop thru each sheet in the workbook and call the PrintOut method on each sheet...
For Each sht in ThisWorkbook.Sheets
sht.PrintOut
Next
--
Jim Cone
Portland, Oregon USA .
http://www.mediafire.com/PrimitiveSoftware .
(Special Print add-in: rows to repeat at bottom)

Marvellous, thank you, Jim. I'll give it a try. As I assuming
correctly that I can put this between something like Sub
PrintAllWorksheetsAtOnce() and then end with an end sub deal - the
usual macro beginning and ending? Or am I wrong?

I don't have a sheet with multiple tabs at home so will try it out at
the office tomorrow with the same workbook my boss printing yesterday.

The neat things about this is that I'm lucky in that we're still on
Excel 2003 at my current contract and I can add a button to my lovely,
lovely Excel11.xlb with this print code once I figure out if it works
for me and then can add it to the rest of my print buttons.

Man, p.s., the ribbon has _nothing_ on my Excel11.xlbs and Excel.xlbs!
I'm still hoping they'll in future allow us to fully integrate our
"old" toolbars with the huge power of the customizations from the
older Excels in the newer office versions which suck pretty big time
as they stand now. The QATs just don't come close, either! The
current ribbon idea sucks big time, too, because it doesn't come close
to the one-button access to absolutely everything one needs that we
have with our own custom toolbars. Gotta tell you, no-one can match
my speed with my customizations! <g

Thanks!

:oD



"StargateFan"
wrote in message
. ..
I've run into this situation before and feel it's time to find a
solution.

At work now it's a wonderful default each new contract has where our
printers are set to print everything double-sided to be more green and
environmentally responsible.

However, it's a problem when one has to print a workbook of many
individual sheets (tabs). If you select to print the entire workbook,
it treats the individual sheet components as if it were one big file
and prints the entire workbook in one go rather than going ahead and
printing the document, but treating the sheets as individual documents
and not printing any two given sheets on the same piece of paper.

Surely there's some nifty code out there that will allow us to select
the "print entire workbook" option but that will treat the output as
if we laboriously printed each sheet individually?

Or am I dreaming of castles in the sky ... or however that old saying
goes ... (???) <g