Copying Chart to another Workbook without links

[Richard Buttrey]

Is there any way that a chart can be copied from one workbook, and
pasted into another without the references to the source data?

I'm already copying text, formulae and numbers in the first w/s, and
pasting it as values in the second w/s, so that I can send the second
w/s to someone else without them needing all the original data.

Now that I've added a chart within the area I copy and paste, it won't
paste with the rest of the text/numbers, and when I select it in a
second operation and paste it separately, it pastes with references to
the originating workbook

i.e. what I'm trying to do I suppose is paste a 'picture' of the
chart. It doesn't need to be updated when the original data changes.

TIA

--
"We are with Europe, but not of it. We are linked but not combined.
We are associated but not absorbed. And should European statesmen address
us and say, 'Shall we speak for thee?', we should reply,
'Nay Sir, for we dwell among our own people'."
Winston Churchill - 1953

[John Walkenbach]

Activate the chart. Press the Shift key and choose Edit * Copy Picture. Then
click any cell and choose Edit * Paste. That will create a picture of your
chart -- a "dead" chart that is not linked to any data.

John Walkenbach
For Excel tips, macros, & downloads...
http://j-walk.com/ss

"Richard Buttrey" <ric...@buttrey.u-net.com> wrote in message
news:3c5168b4...@news.u-net.com...

[Jon Peltier]

John's approach is good, unless you want to be able to format the elements of
this unlinked chart.

It is possible to unlink the chart from its data, using this simple kind of
approach:

''' =====================================================
Sub DelinkChartFromData()
''' Thanks to Tushar Mehta
Dim mySeries As Series
If TypeName(Selection) <> "ChartObject" _
And TypeName(Selection) <> "Chart" _
And TypeName(Selection) <> "ChartArea" Then
MsgBox "This functionality is available only for charts " _
& "or chart objects"
Exit Sub
End If
On Error Resume Next
If TypeName(Selection) = "ChartObject" Then _
Selection.Chart.Activate _
Else Selection.Activate
On Error GoTo 0
For Each mySeries In ActiveChart.SeriesCollection
mySeries.XValues = mySeries.XValues
mySeries.Values = mySeries.Values
Next mySeries
End Sub
''' =====================================================

What this does is take the values and xvalues from the source range in the
worksheet, put them in an array, and save the array in the series formula.
Originally this formula looks like this:

=SERIES(,Sheet1!$D$4:$D$15,Sheet1!$E$4:$E$15,1)

using the simple randdom data I stuck into my worksheet, and the modified
formula looks like this:

=SERIES(,{15.031454211777,8.1383538616338,10.9998151055087,13.410691201042,10
.6248254334369,10.6371217842496,11.3995831152254,19.5323146287637,11.08850908
85436,4.4742985867933,6.31296089699334,3.81550937155244},{116855.089242441,23
9494.950013403,185567.581627991,64882.6685031719,318350.869651478,129554.3471
63989,458906.997532184,31286.2635801765,368058.120913394,304468.990447535,295
618.685237458,376771.217071102},1)

Notice the arrays for x and y values (comma separated lists within curly
brackets). The problem with this approach is that Excel chokes on a series
definition formula longer than around 1000 characters. But our formula
obviously carries a lot of extra characters, so I have modified the procedure
above to this, which excises the insignificant digits:

''' =====================================================
Sub XYValuesToSeriesFormula()
Dim nPts As Long, iPts As Long
Dim xArray As String, yArray As String
Dim xVals, yVals
Dim ChtSeries As Series
Dim iChars As Integer

For Each ChtSeries In ActiveChart.SeriesCollection
nPts = ChtSeries.Points.Count
xArray = ""
yArray = ""
xVals = ChtSeries.XValues
yVals = ChtSeries.Values
For iPts = 1 To nPts
If IsNumeric(xVals(iPts)) Then
' shorten numbers (remove excess digits)
iChars = WorksheetFunction.Max(InStr(CStr(xVals(iPts)), "."), 5)
xArray = xArray & Left(CStr(xVals(iPts)), iChars) & ","
Else
' put quotes around string values
xArray = xArray & """" & xVals(iPts) & ""","
End If
' shorten numbers (remove excess digits)
iChars = WorksheetFunction.Max(InStr(CStr(yVals(iPts)), "."), 5)
yArray = yArray & Left(CStr(yVals(iPts)), iChars) & ","
Next
' remove final comma
xArray = Left(xArray, Len(xArray) - 1)
yArray = Left(yArray, Len(yArray) - 1)
ChtSeries.Formula = "=SERIES(,{" & xArray & "},{" & yArray & "},1)"
Next
End Sub
''' =====================================================

Look at the new, svelter series formula:

=SERIES(,{15.03,8.138,10.99,13.41,10.62,10.63,11.39,19.53,11.08,4.474,6.312,3
.815},{116855,239494,185567,64882,318350,129554,458906,31286,368058,304468,29
5618,376771},1)

- Jon
_______

In article <#imdP5apBHA.680@tkmsftngp04>, John Walkenbach said...

[Jon Peltier]

If anyone is interested, I've punched up the code a bit and posted it on my
website:

http://www.geocities.com/jonpeltier/Excel/Charts/DelinkChartData.html

- Jon
_______

In article <ej1G1#rpBHA.1964@tkmsftngp07>, jonpe...@yahoo.com says...