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 Thanks in advance!