Copying a chart and unlinking it from the original workbook

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Is there a way of copyiong a chart from one workbook to another and
'unlinking' it, such that it does not update from the original worksheet if
the original data is changed. Does it need to be copied as a picture or
similar??

Thanks in advance.
 
Thanks Andy, Jon Peltier's site was very useful.

I'm using the macro for multiple series with lots of data, however I get an
error at line 13 (i think) saying type mismatch. I thought it may be because
my chart has scatter and line chart data on it, so I deleted the line data
and tried again, but got the same result. I'm somewhat of a novice at VBA,
can you help any further??

Error message in VB reads 'Runtime Error: '13': Type Mismatch'
 
Hi,

That's an error type 13, which suggests the wrong type of value is being
placed in a variable (eg. letter where a number is required).
I have tried various things to re create you problem but can not.

If you want email your workbook to me, off newsgroup, and I will take a
look.

Cheers
Andy
 
Unfortunately can't email any data due to company restrictions. Spoil-sports!

If the problem is the wrong type of variable maybe I need to check the data
to make sure its in number format? Am I along the right lines? All of the
data points are generated by formulae; not sure if this is relevent. Also,
some of the data points are #N/A values, but I need to keep them in this form
so that the original chart uses 'best fit' values across null values, rather
than reverting to zero. Perhaps I need to modify the code to compensate for
#N/A values?

I also have a cell link for the series name (for use in the legend) which I
will also need to unlink. Is this relevent?

That's all I can think of really. As I said, still a bit of a novice with VBA.

Thanks for your help.
 
I did test with NA() and blank cells both of these did not present a
problem.

Can you identify the actual line of code that produces the error? When
the error dialog comes up choose Debug. The offending line should be
hilited.

How much data do you have in terms of rows and columns?

Cheers
Andy
 
Thanks for the continued help Andy, I have decided in the short term to use
the copy puicture method on Jon's website, but I will need to be able to edit
unlinked charts in the future so it'd be useful if we could nail this down.

I have, in an attempt to get it working first of all, cut my chart to down
to a single series of data with 200 x-values and 200 y-values, plotted as
scatter chart with no legend or axis titles. I have copied over the original
formulae for the data points with Paste Special | Values and deleted all
cells containing #N/A, but i've kept them in the range. I've also re-copied
the code from Jon's website into my VB editor (VBA Project
(Workbook)\Microsoft Excel Objects\ThisWorkbook in the navigation window on
the left).

Now when I run it from within excel, I get an error message saying 400 and
nothing else, with Ok and Help button but no Debug button. If I highlight the
chart and run from within VB editor I get "Run-time error '1004':
Application-defined or object-defined error". I am now utterly confused, and
still unsure as to which line of code is my nemesis!

Getting to the point where I say hang it all and just continue to use the
copy picture method, but it would be a useful macro to have working...
 
Picture is the best way to go. The 1004 error is probably due to the
length of the formula series exceeding 255 characters. A quick test of
this is to select the data series and then with the cursor in the
formula bar press F9. This will convert formula to values. If the
conversion is incomplete you will get an error.

Cheers
Andy
 
Yep, you're right. It works with no problems on a much smaller data set.
However I was under the impression (from Jon's website) that the code
specifically dealt with longer number data sets. Maybe mine was just too
excessive!

Thanks anyway for your efforts Andy. Will stick with the easy 'Copy Picture'
option til I learn to be as good as you guys.
 
The code does reduce the number of number of decimal places used but
apparently even this is not enough to keep the data values below the
required length.
Note my 255 length restriction should be approx 1000 characters, as
pointed out on Jon's page.

Cheers
Andy
 
No, it's supposedly 1000 characters (or 1024) in the whole formula, but
that seems to have been evenly apportioned among the four arguments of
the SERIES formula. 250 characters is only going to be enough for at
most about 80 points, assuming two digits max and a comma between
values. More practically it craps out around 20 to 40 points.

My code merely trims a few meaningless digits to allow a few more points
to be squeezed in. In general, I try to maintain the data for a chart
somewhere, hidden if necessary. The storage requirements for this
approach are minuscule, but the difficulties it saves are enormous.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______
 
Back
Top