Printing Settings across many worksheets in workbook

lanean

New Member
Joined
Oct 21, 2014
Messages
3
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...
 

Some videos you may like

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.

pbornemeier

Well-known Member
Joined
May 24, 2005
Messages
3,822
Is the printarea range the same on each worksheet?
If so, what is it? Some address range like A1:BQ250
 

lanean

New Member
Joined
Oct 21, 2014
Messages
3
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. :ROFLMAO: Advice?
 

pbornemeier

Well-known Member
Joined
May 24, 2005
Messages
3,822
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,102,578
Messages
5,487,668
Members
407,609
Latest member
stellaa9x

This Week's Hot Topics

  • Timer in VBA - Stop, Start, Pause and Reset
    [CODE=vba][/CODE] Option Explicit Dim CmdStop As Boolean Dim Paused As Boolean Dim Start Dim TimerValue As Date Dim pausedTime As Date Sub...
  • how to updates multiple rows in muliselect listbox
    Hello everyone. I need help with below code. code is only chaning 1st row in mulitiselect list box. i know issue with code...
  • Delete Row from Table
    I am trying to delete a row from a table using VBA using a named range to find what I need to delete. My Range is finding the right cell. In the...
  • Assigning to a variable
    I have a for each block where I want to assign the value in column 5 of the found row to the variable Serv. [CODE=vba] For Each ws In...
  • Way to verify information
    Hi All, I don't know what to call this formula, and therefore can't search. I have a spreadsheet with information I want to reference...
  • Active Cell Address – Inactive Sheet
    How to use VBA to get the cell address of the active cell in an inactive worksheet and then place that cell address in a location on the current...
Top