macro not ending

[Josie]

I'm using a simple macro to draw bubble charts using just
6 cells of data. The macro also uses a single cell of
text as the title of the chart and the name of the new
sheet the chart becomes. I then paste the sheets into a
different file (by hand). When I return to the original
file to do the next chart, when I change the data in the 6
cells and draw the new chart, it changes the position of
the bubbles in all the old charts. Charts retain,
however, their title and sheet name.

 

[Tom Ogilvy]

On the copied chart, click on one of the bubbles and look at the formula
bar. Does it show a link back to your original data?

 

[Guest]

Yes is does.

In VB is there a command for the equivalent of 'paste
values', that would cut the link to the data on the
worksheet?

Josie

 

[Tom Ogilvy]

Select the chart, then hold down the shift key and do Edit=>Copy Picture.
This will copy the chart as a picture and it won't have any links (won't be
editable either).

If you need a macro, turn on the macro recorder while you do it manually.

another approach it select each series in the chart (as you did to check)
and go to the formula bar, highlight the formula and do F9 - this evaluates
the formula - so you will see arrays of values. Hit enter. This converts
the reference to the hard coded values. This only works for small amounts
of data since you are limited by the string length, but it if works, it
certainly breaks the link. You may have to do it for the title and axes as
well.