Excel Deleting embedded graphs during worksheet copy

[DaveH]

I have a number of graphs imbedded in a worksheet, all using data from that
worksheet and only from the worksheet.

I would like to run a different analysis on the data and keep the original
analysis so I am trying to create a copy the worksheet and embedded charts in
the same workbood.

When I select "move or copy" selected sheets, with the "create a copy" box
checked, the worksheet is copied to a new tab, but the graphs are dropped.

There are a large number of graphs so I would like to not have to redefine
the ranges to point to the new data on each chart as would happen if I copied
the graphs from the old worksheet to the new, chartless worksheet.

This used to work as you world expect in 2003, but I can repeat this
behavior on any new or old workbook in 2007.

Is there a fix for this or a workaround for this bug?

DaveH

 

[Shane Devenshire]

Hi,

You might try copying as follows.

1. Select the whole sheet by clicking the top left corner
2. Press Ctrl+C
3. Move to a blank sheet and select cell A1
4. Press Ctrl+V

 

[DaveH]

Shane

I'm afraid that doesn't work on a number of levels

Highlighting the whole sheet and copy/pasting to a new sheet only grabs the
cells, the embedded chart is left behind - I suspect that the same mechanism
used to copy sheets is being used for the cell copies.

If I continue with your logic, I could try copying the worksheet as you
described then performing a second copy action to copy the embedded charts
into the new worksheet. This results in the new chart ranges pointing back
to the original data, which ends us right back where we started.

If I recall in 2003 (its been a while since I had 2003), you could copy
objects (i.e. charts) with the cell copy in this manner, but the copy would
also point back to the original data.

The workaround I've developed for this is incredibly convoluted, involving
multiple copy actions, temporary worksheets, tab renames, and quit without
saves in order to trick excel into making copies of the charts that point to
the copy of the data.

It would be possible to do this with a VBA macro, but would take a lot of
work given all the ways that charts can reference the worksheet.

Thanks though

DaveH