_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 Tek-Tips, 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.