Hi,
Situation is a little complicated and so please bear with this one. Here it goes:
Background - In our department, Business Analysts frequently use a document called the 11x17 document for business case analysis purposes that they print out. Normally, analysts will have over 30 - 40 11x17 documents they need to analyze for a given client and given the number of documents they need to analyze on paper, they need to print them.
Issue - The current problem analysts face is that MS Excel automatically places "Page Breaks" for each 11x17 in rows/columns depending on the 100% zoom factor and the amount of data on the page. While this is 'ok', its hard to analyze the numbers if not all the rows/columns are not all shown on the same sheet.
Automation - I developed a Print 11x17 macro to handle this situation. It provides the user with the flexibility of selecting the directory where their 11x17s are located and gives them the option of selecting certain "print" criteria. What the automation does it a brute force method of placing horizontal/vertical page breaks by iterating through each column, calculting the width of the column points/pixels, and setting page breaks at every "clean" point. What I mean by "clean point" is that if you take a look at the 11x17 document that I've added, You'll notice that the second row consists of demographic filters (indicated as merged cells - i.e. Sales vs. Engineer) with statistical data following that.
Automation Issue - The issue that I'm seriously having trouble trying to fix is that even though the automation 'seems' to be placing the breaks correctly by measuring the 11x17 using the brute force method, the zoom for each page within each worksheet becomes very small. If you go to print preview, you'll notice that the zoom feature shrinks to anywhere between 10 - 30%. I don't want that. I want to keep the 100 at a 100% and add all the page breaks at every 'clean' point.
How can I make this achievable? I've spent days on this and I can't find a resolution. If you can shed some light to this fix (hoping that its an easy fix), that'd be great. Let me know.
How to use the macro -
1. Save the "PRC Data Export.4.7.11x17(y_job_cod
e_name).xl
s" to any directory
2. Run the "Print 11x17s Automation.xls" by clicking the "Save Print Settings for my 11x17s" in the 'Print 11x17 Automation' worksheet.
3. Don't check anything on the userform. When prompted for the data file, select the file you saved in (1).
After you run it, notice how the output print-preview shrinks each worksheets page to a small %.
