Printing Settings across many worksheets in workbook

[lanean]

Hi all,

I am struggling with a printing issue for a workbook with many sheets that I update & print on a weekly basis.

Background - I am using Excel 2007, & pretty sure I'm working with a Windows 7 system.

My workbook has many sheets, each with 4 graphs on them, which I have isolated on each sheet as the "print area" with the data below, outside of the print area. I add to the data on each sheet daily & update the data set for the graphs - by moving the chart area boxes - once a week before I print them. I know now how to change the page set up across my entire workbook so that they all print on the same size paper (11x17) & fit to 1 page wide x 2 pages long (in essence, I need 2 graphs per 11x17 page, making each worksheet 2 pages), but even in my print preview, there are some sheets that split the 2nd or 3rd graph onto the other page! I have changed the settings within each worksheet multiple times so that it does not print that way, but each week I go to print, more sheets do the same thing, even though I have not changed the print area, settings, or even moved or resized the charts on ANY of the sheets -- the only change I am making is to the data set! Is there any way I can, a.) make a change to the page set up across the workbook to make sure it does not split the graphs, even if I have to do it each week, or b.) make a change within each worksheet to do the same, but that will save from week to week, instead of having to do it every time I print? I want the workbook to print the exact same way every week, with only the data in the charts changing.

I hope my question is clear enough & I've given enough information; I'm tired of struggling to print this document every week!! Thank you for any light you can shed on this situation...

 

[pbornemeier]

Is the printarea range the same on each worksheet?
If so, what is it? Some address range like A1:BQ250

 

[lanean]

Is the printarea range the same on each worksheet?
If so, what is it? Some address range like A1:BQ250

The print area is not the exact same on each worksheet, though it is close (for example, one sheet is A1:Y82, while another is A1:X82, & another is A1:Y85)... when I initially set up the workbook, I adjusted each worksheet individually & had the print area sized for the graphs on that page. I am not moving or adjusting the print area at all when I print it each week, just changing data.

A guy I work with suggested that it might be because the graphs are automatically sizing themselves as I add & shift the data set. As an example, whereas the dates for the data for a previous week were 9/01/14 - 9/30/14, the graph may have had to adjust in size for the date range of 9/17/14 - 10/17/14 (adding the additional digit), minutely enough to barely notice it, but enough to make the graphs push onto the next page. Which is fine if they do automatically adjust, but not go off the page... I tried setting up a page break but it doesn't seem to make a difference. The graph just divides over the page break. Advice?

 

[pbornemeier]

The data may be outside of the PrintArea, but if the data under the PrintArea widens a columnwidth inside the PrintArea, then the extra width will widen the graph and make it break over a page. The problem could likely be fixed if you moved all of the data so it was below the bottomost chart and right of the rightmost chart. The columnwidths that contained the charts would never change (unless moved manually).

For a VBA solution that should handle changes in column widths, I need to know how are your charts arranged on each worksheet? 1 row of 4? 1 column of 4? 2 rows of 2 columns? Hopefully the same on each worksheet.