Excel Hidden Feature: Printing Disjointed Ranges on ONE Page 5

[DaleWatson123321]

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> E ...C …<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) 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 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.

 

[Dreamboat]

http://www.VictimPhotos.com

http://www.PiersonTech.com

Brainbench MVP for Microsoft Word
techsupportgirl@home.com

 

[euskadi]

Thanks for the reminder! Now I remember using this on some spreadsheets about 6 years ago, but had forgotten the trick.

I'm not sure if it's really a bug, or just something that MS overlooks all the time everything they do a new release. At least the help-file people know about it, there's a &quot;I can't find the Copy Picture command&quot; listing in the help file. Interestingly, I also couldn't find the command anywhere when I try to customize the menus.

 

[JVFriederick]

euskadi
Ditto for &quot;Pick From List&quot;, which would be real handy under control of a macro.

 

[thwingr]

Great tip! I've been showing what I've learned with anyone I can trap!

Two sub-hints:

<Alt><Shift><Mouse Edit> does not work ... as you say.
But <Ctrl><Shift><Mouse Edit> does display the Copy Picture selection.

Instead of using <Control><F3> I use the Name box to assign range names.

 

[DaleWatson123321]

thwingr,

Thanks for your contribution. Short-cuts are always useful.

Here's another suggestion for anyone interested in making &quot;Copy Picture&quot; even MORE accessible - by placing &quot;Copy Picture&quot; as an &quot;icon&quot; on your toolbar. Once on the toolbar, all one has to do (after highlighting the range) is use: <Alt> C. ...then <Ctrl> V to Paste the Picture.

Here's the procedure... two parts ...A and B...

A) You first need to create the &quot;macro&quot; (for copying the picture). This needs to be placed in your &quot;Personal.xls&quot; workbook.

1) The &quot;Personal&quot; workbook is normally hidden. To unhide it, use &quot;Window - Unhide&quot;.

2) Then, to go into the Visual Basic Editor, use <Alt> <F11>.

3) To create a &quot;Sub routine&quot; ... for example called &quot;CopyPicture&quot; ... Type &quot;Sub CopyPicture&quot; and then hit <Enter>.

4) Use <Alt> <Tab> to return to the Tek-Tips screen.

5) Highlight and copy the following, using <Ctrl> C ... Selection.CopyPicture Appearance:=xlScreen, Format:=xlPicture

6) Use <Alt> <Tab> to return to your macro screen.

7) Then use <Ctrl> V to paste the code. Paste it onto the line between &quot;Sub CopyPicture()&quot; and &quot;End Sub&quot;.

8) Use <Alt> Q to exit from the Visual Basic Editor.


Part B: - Create the toolbar icon, and attach the macro you just created.

1) Right-Click on blank space on the toolbar.

2) Select &quot;Customize&quot; on the bottom of the pulldown menu.

3) Under &quot;Commands&quot;, scroll down until you come to &quot;Macros&quot;

4) After choosing &quot;Macros&quot;, drag the &quot;Happy Face - Custom Button&quot; onto the toolbar.

5) Right-click on the &quot;Happy Face&quot; icon, and select &quot;Text Only (Always) from the pulldown menu.

6) Because the icon will change to &quot;Copy Picture&quot;, with the &quot;&&quot; character in front of the &quot;C&quot;, this means you will be able to use <Alt> C to activate this icon - i.e. you can hold down the <Alt> key and hit &quot;C&quot;.

7) BEFORE your icon can be utilized, however, you first need to attach your &quot;CopyPicture&quot; icon.

8) Right-click on the toolbar, and choose &quot;Customize&quot;.

9) Right-click on your &quot;Copy Picture&quot; icon, and choose &quot;Assign Macro&quot; (at the bottom of the pulldown menu)

10) Select your &quot;CopyPicture&quot; macro from your &quot;Personal.xls&quot; workbook, and click &quot;OK&quot;.

11) Close out of the &quot;Customize&quot; window.

12) You can now re-hide your &quot;Personal.xls&quot; workbook – by using &quot;Window - Hide&quot;

13) Important – Don’t forget to SAVE your &quot;Personal.xls&quot; workbook – either before or when you exit from Excel.

This might be a &quot;little up-front work&quot;, but well worth it because setting up such &quot;custom icons&quot; – especially with &quot;Hot keys&quot; – can dramatically improve your speed in Excel. &quot;You can put me up against any ‘mouse-jockey’ ANYTIME, and I’ll beat them across the finish line before they get half-way around the track.&quot;

I truly hope this helps, and that your speed will increase with such &quot;short-cuts&quot;.

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

 

[DaleWatson123321]

Update... to correct a slight error.

In section &quot;B&quot; item 7), the last word of the sentence should read &quot;macro&quot; - NOT &quot;icon&quot;.

It should read as follows...

7) BEFORE your icon can be utilized, however, you first need to attach your &quot;CopyPicture&quot; macro.

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

 

[thwingr]

Ok, this is gonna be really embarrasing. If you add the camera button from the tools category to your command bar it does the same thing as we are talking about. According to page 410-413 of &quot;Running MS Excel 97&quot; the main difference between the Camera button and the copy picture command is that the camera button automatically adds the link and the copy picture command doesn't.

I stumbled across this reference (buried in a chapter on graphics) when looking in the index for information about the vba picture object. I have found no other mention of the camera button anywhere, not even in the help files for vba or excel.

 

[DaleWatson123321]

thwingr,

GOOD ONE ... definitely worthy of a STAR.

The only thing I would add, being a &quot;keyboard is FAR quicker than the mouse&quot; type... is to change the icon to &quot;Text Only Always&quot; - which will change it to &quot;Camera&quot;. This will then allow the use of <Alt> C .

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