Problems printing multiple worksheets with the same printer settings
Hi everybody! I am banging my head against the wall with some printing issues. The scenario is that I have a collection of worksheets within one workbook. Each of these worksheets is unique in that they are different lengths and have different information in the header and footer.
My goal is to give the user the print dialog box so they can specify printer settings (duplex, black and white, etc) and then be able to print all the worksheets with the same settings. If you choose "Entire Workbook" in the print dialog box it only prints the first worksheet with the settings the user chose (because Excel dispatches each as a separate print job).
I tried two possible workarounds, neither of which I got fully working.
• The first was to make a custom box that allowed the user to tell me their print settings, capture those and then loop through and automate the printing of each worksheet individually. The problem is that I can't figure out how to change the printer settings with VBA (they don't get recorded in a macro, I've tried that).
• Second scenario is to copy and paste all the worksheets into one big worksheet and insert HPageBreaks between them. This worked wonderfully for formatting; all the printer settings get applied to everything. But here the header (worksheet name) and footer (page numbers) information is not maintained.
If anyone has any ideas on how I can fix my two scenarios, or a new one that I haven't thought of I would LOVE to hear about it. My head hurts from thinking about this :confused: Thanks in advance!
Re: Problems printing multiple worksheets with the same printer settings
Record a macro for Page setup..
and switch to printview.. and record macro for any adjustments there
record one for each sheet...
then compare... find which lines change for each one.. then create a function that you can pass in the variable that change
then loop through all sheets.. setting them up.. then select all sheets and print
done it many times ;)
Re: Problems printing multiple worksheets with the same printer settings
Quote:
Originally Posted by mlamb2005
If you choose "Entire Workbook" in the print dialog box it only prints the first worksheet with the settings the user chose (because Excel dispatches each as a separate print job).
Well ... that is one way of looking at it, but if it were absolutely true, each section would start renumbering pages at 1, which is not the case. Page numbers are strictly sequential through to the end of the document. Each sheet does have its own set of Page Setup parameters, which is a very good thing. There is no problem going through and changing these parameters programatically. You may have to read each parameter before you change it and save the original values so that you can restore them. I agree, there are quite a few Page Setup parmeters to deal with!
One point: as well as setting "Entire Workbook", you probably know that you can select (activate) whatever sheets you want to print. One thing to beware of ... each printer driver handles the changes in page formats differently! Adobe Distiller does a really good job at mixed page formats, except it always barfs when the Page Setup Print Quality parameter changes from one page to the next.
