People worry about having multiple copies of information, and they want a
simple way to switch formats around. It is far easier to have two copies of
the information (e.g., of a chart), and format each copy for its specific
role. If both are linked to the same source data, they will accurately show
the proper data.
If your target paper size changes, you need a differently formatted chart.
Why not have a different chart for each size paper (or for each size graphic
on any piece of paper), and let your copy routine decide which one to use?
Simpler doesn't necessarily mean smaller or using fewer sheets or whatever.
Simpler means saving yourself time and energy.
- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______
"Fredrik E. Nilsen" wrote in message
...
On Sat, 21 Apr 2007 20:10:14 -0400, "Jon Peltier"
wrote:
Size and format the chart appropriately before copying it, so you don't
have
to do any formatting in the target application. Pick a size for the chart
(I'll use 2.5" by 3" in my description that follows). With the chart on
its
own sheet (a chart sheet), change the margins so the chart fits the size
you
designate. The paper is 8.5" tall by 11" wide, so you need two margins 3"
wide at the top and bottom, and two margins 4" wide at the left and right.
Now when copied and pasted the chart will be the correct size.
When you copy a chart as a chart, the chart itself is not copied. Instead,
Office merely keeps track of the reference of the chart. Office then
includes either the reference (if linked), or it actually copies the
chart,
when you paste. So if you delete the chart, the reference is also deleted.
Thanks Jon, I hadn't thought of that approach.
Two problems come to mind:
This will only work if the paper size always is the same. I can, of
course, change the paper size to for example A4 but then I have to
revert it to the original size after the chart is copied.
Is there a way in VBA to reset the margins after the chart is copied?
--
Fredrik E. Nilsen