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

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

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

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

On Tue, 29 Mar 2011 20:27:52 -0700, Gord Dibben
wrote:

Be careful with how much you customize your Toolbars.

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

Thanks but I've been using them for many, many years now ... in fact,
I can't even remember how long ago it was when I first started.
Certainly can't remember in the last 20 years when I didn't use them.
It's just that in the old days, my xlbs, etc., and backups I carried
around on floppies that I carried in a clear plastic floppy box case
that would hold about 10 floppies <g. Sure is a far cry from the
small 16gig flash drive I carry around now. And I say small because I
could use one that is larger it's just that present economy and salary
doesn't allow it in the budget <g. Yet in all this time of using
them, doing contract work in last 20 years, never had a problem even
though loaded them up in each new job and used several different host
computers in any given year. They're great.

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

I do, thanks.

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

I have floating toolbars on many of my indivdual sheets, but I like
the main look of my Excel with the xlb as it stands with my own
standard 5 extra toolbars. It's pretty kewl. Again, the faceIDs have
much of the same limitations as the QATs, you can' customize the icons
and you're stuck with the selection. However, I have made them work
and they are handy esp. for those workbooks that require a new sheet
be created from a master periodically. In the beginning I used
buttons that I'd place on the sheet. These worksheet buttons would in
effect be replicated on each new sheet and would make the workbook's
size mount up pretty quickly <g. So in those cases, I've used these
floating toolbars instead for some time now even though it's a
relatively new skill for me. I've used these vb toolbars for about
the last 5 or 6 years or so only. But my xlbs provide my main
interface and which has made the odd co-worker comment that my Excel
interface looks like a darned cockpit or something, it looks so
complicated according to them. That always makes me laugh. It's not,
it's made my life so incredibly simple, something M$ again didn't
consider when they decided to "improve" <hah. Anyway, I'm lucky. I
still get contracts with the older versions, thank goodness. And
since it's government, that will likely continue for some time.
Because, personally, if M$ doesn't smarten up, I will continue stopped
at 2003 since I refuse to upgrade to newer versions. Bad enough I'm
forced to use them at work. There are alternatives out there to
consider in lieu of, so hopefully M$ keeps that in mind.

:oD

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

Marvellous! You're the best. If these work as well as I believe they
will, I'll finally have a solution to the desired green necessity of
double-sided printing that still produces workbook printouts properly
separated into their individual parts. This is great.

Fiscal year end has been the usual fun time so absolutely didn't have
a chance to breathe today let alone try the code <lol. But now that
I have actual whole macros to work with, maybe I can sneak a moment to
trial these tomorrow at some point. Boss' calendar is, miracle of
miracles, clear of meetings tomorrow, so I might just find a moment or
two to to try these out <g. Desirable since she gets so many meeting
documents to print out in any given week.

Thanks! :oD


On Tue, 29 Mar 2011 20:46:15 -0700, "Jim Cone"
wrote:

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.

On Tue, 29 Mar 2011 20:46:15 -0700, "Jim Cone"
wrote:

[snip]

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.

Yup, I tried those add-ons. Hah, what a joke. I had even _more_
buttons to cycle through to get to the classic menu and then to get to
the bits that were my own toolbars and nothing was in the right place.
Ugh! The power of my toolbars was completely lost.

What I'd love to see (hear this Micro$oft!!) is to have the ability to
switch the ribbon off COMPLETELY and get back the classic menu with
our own customizations EXACTLY as we had it before for those of us who
want to do that. Give people the option. That's the only way to
retian the power users such as myself.

The ribbon has very little advantage over the classic menu. Sure,
it's visually based rather than text based and all the new generations
won't know any different but you get no advantages in speed. You
still need to click in twenty million places. At least in the old
versions, you had the power to customize to the nth degree even
placing your own icons wherever the he11 you wanted them and then
attaching macros when the feature was not natively available. So the
older versions allow us to get the fastest interface possible. And is
it difficult to do? Not.

I don't doubt that with complex programming XML or whatever the QATs
are based on, people can come up with customizations, but the measly
little bit of room the QATs provide is nothing. I have 5 extra
toolbars on both my Word and Excel, with 1 or 2 parallell bars running
on either side in each. And the icons I've standardized in all
applications, including PowerPoint and WordPerfect so that all
functions that are identical carry the same icon across the board in
all the apps.

Ah well. Fortunately, one of my possible futures is to work at my own
business and I certainly then won't have these limitations. Since my
output work will be published in graphics form and PDF, I can work in
practically any app I want. I imagine WordPerfect and OpenOffice will
keep up with PDF technology so they should remain as options, too.
And anyway, may just decide to publish text/graphics in graphic format
such as gif or jpg anyway.

Anyway, time will tell how M$ recovers from this blunder. <g