Archive of Mr Excel Message Board


Back to Printing in Excel archive index
Back to archive home



Printing multiple page setups

Posted by peter rouse on February 15, 2002 7:03 AM
I have a spreadsheet covering about 250 rows, parts of which I want to print.
Some rows have a length more suitable for Landscape, some more suitable for portrait.
Using Set Print Area, and highlighting the various non contiguous areas using the control key, I can create a sheet with two or more seperate areas for printing. But I cannot select these areas for individual page setup, although when I go to Print Preview, it shows each of the areas I have selected as seperate pages. Creating a landscape format for page 1 means pages 2 and 3 take landscape. and vice verca.
Is there a way, in a single spreadsheet, of having multiple set print areas with different page formats?

Re: Printing multiple page setups

Posted by Dreamboat on February 15, 2002 11:47 AM
A friend of mine wrote this not too long ago, and I have out and out stolen it for you because I know that he would not mind:

How To Print Disjointed Ranges on ONE Page

OVERVIEW:

This EXCELLENT "Hidden Feature" permits Excel users to print disjointed ranges on ONE page.

BACKGROUND:

I've been complaining "long and loud" about Excel's printing limitations, specifically regarding the fairly common need to print on ONE page, "disjointed" ranges located on different sheets, or located on the same sheet but in different columns requiring different column widths.

Using Excel's conventional means of specifying the print ranges (in Page Setup - Print Area), Excel allows the user to specify the disjointed ranges by separating each range with a comma. However, Excel automatically forces a "Page-Break" between each range, thereby effectively eliminating the option to have such disjointed ranges printed on ONE page.

In spite of having complained directly to Microsoft - to have them eliminate the "forced page-break", as well as posting this problem months ago on <another tech site>, no solution was ever offered.

Just today when experimenting with Excel, I happened to discover an ideal option ...one that causes me to "take back everything I ever said about Excel's printing limitations".

THE SOLUTION:

This option utilizes a "HIDDEN" feature - called the "Copy Picture" option – which can be linked to the original data source. By Linking these "pictures objects", any changes made to the "original data" will automatically be reflected in the "picture objects". These changes not only include data input, but also insertion or deletion of columns or rows within the object’s named range.

This feature is TRULY HIDDEN... It is actually activated from Excel's menu - under the "Edit" dropdown list. However, if you click on "Edit", you'll notice that "Copy Picture" does NOT show up on the dropdown list - NOT EVEN "grayed out". This has GOT to be considered a BUG in Excel, or at the very least a "REAL DUMB OVERSIGHT" on the part of Microsoft.

So how DO you activate this ? ...as follows... Hold down the <Alt> and <Shift> keys, and hit “E” (for “Edit” on Excel’s menu), then hit “C” (for “Copy Picture”), and then <Enter>. Note: this assumes you have FIRST highlighted the range you wish to copy. Also note, that holding down the <Alt> and <Shift> keys and using the mouse to click on “Edit” on the menu does NOT work – i.e. you HAVE to use the “E” on the keyboard.

RECOMMENDED STEPS:

1) First assign a Range NAME to each of the (disjointed) ranges you want to print on the ONE page, ...or if necessary, the data can be printed on more than one page. But, if desired, YOU can decide where to place a “forced page-break”.

2) Then, for each of the named ranges, do the following:

a) Highlight the range …you could use the “GoTo” key <F5> and enter the name.

b) Use <Alt> <Shift> - i.e. while holding down the <Alt> and <Shift> keys, Hit "E" (then "let go" of the keys) ...then Hit "C" ...then <Enter>. (accept the default settings in the “Copy Picture” window).

c) Go to the separate sheet from which you will be printing these disjointed objects.

d) Paste the object – use <Control> V.

e) You must now Link the object so future changes in the spreadsheet will be reflected. With the object selected (which is the case when you first create it), immediately link the object by: Type the equals character ( = ), followed by the range name, and <Enter>.

f) Freely “drag” the object to the location you prefer. Note: If desired, you can also squeeze or stretch the object.

That’s it, that’s all. You can now go ahead and freely move and size many of these objects to fit on ONE page.

You are also afforded the flexibility of creating professional looking, customized forms, where these separate objects are “fed” data from other sources, and you are are NO LONGER restricted by the conventional method of being forced to use the SAME columns for DIFFERENT data which really requires its OWN column widths.

A Final Tip – Assigning a Range Name – the EASY way:

1) Highlight the range.

2) Use <Control> <F3> ...i.e. hold down the <Control> key and hit the <F3> key.

3) Type the name.

4) Hit <Enter>

Reminder: Don’t create Range Names that “conflict” with cell addresses or numbers. Examples: Don’t use “A1” – instead use “_A1”. Don’t use “1” – instead use “_1”.

I hope you find this information useful. Feedback would be appreciated.

Also, for anyone interested, I’ve created a sample file. Just email me, and I’ll send the file via return email.

Regards, ...Dale Watson dwatson@bsi.gov.mb.ca.

_______
End *article*!




This archive is from the original message board at www.MrExcel.com.
All contents © 1998-2004 MrExcel.com.
Visit our online store to buy searchable CD's with thousands of VBA and Excel answers.
Microsoft Excel is a registered trademark of the Microsoft Corporation.
MrExcel is a registered trademark of Tickling Keys, Inc.