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)




"StargateFan" <IDon'(E-Mail Removed)>
wrote in message
news:(E-Mail Removed)...
> 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>
>

Be careful with how much you customize your Toolbars.

*.xlb files are easily corrupted and cause problems with Excel.

Make sure you always have backups of you *.xlb files.

Best to create Toolbars and Menus "on the fly" then delete them when closing.

See how at Debra's site.

http://www.contextures.on.ca/xlToolbar02.html

Also look at John Walkenbach's MENUMAKR.xls

http://spreadsheetpage.com/index.php/file/menu_maker/


Gord Dibben MS Excel MVP

On Tue, 29 Mar 2011 21:29:11 -0500, StargateFan
<IDon'(E-Mail Removed)> wrote:

>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.

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" <IDon'(E-Mail Removed)>
wrote in message
news:(E-Mail Removed)...
> On Mon, 28 Mar 2011 19:18:17 -0700, "Jim Cone"
> <(E-Mail Removed)> 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!
>
> D
>
>
>
>>"StargateFan" <IDon'(E-Mail Removed)>
>>wrote in message
>>news:(E-Mail Removed)...
>>> 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>
>