Keep Chart-Delete data?

[Guest]

Hi Group,
Is there a way similar to "past special values" that I can keep a chart and
delete the source data?

 

[Sharad]

For this you can copy the chart as an image (Picture) and paste back as
an image.

Right click the chart and select copy.
Start a winword document (or may be paint brush). Right click there at
paste. The chart gets pasted as a picture.

In execel delete the data , and the chart.
In winword, right click on the chart image and select copy.
In excel paste it where you want.

Sharad

 

[Guest]

Hi Sharad,
This method works, but I am trying to stay out of another application. This
is the code I have so far:
Sub Macro3()
ActiveChart.ChartArea.Copy
ActiveWindow.Visible = False
Selection.Delete
ActiveSheet.Paste
End Sub

The error I get is:
"Run time error '1004':" & "Paste Method of Worksheet Class Failed"

But at this point I can manually paste from the clipboard and get a
"picture", which allows me to go back to the original data and delete it. I
am not sure how to access the system clipboard, which must be differant than
the Worksheet clipboard, although I find this a little puzzeling.
ActiveWindow.Visible = False seems to indicate I am no longer in Excel, but
the immediate window says I am on the Sheet. Both Activeshhet.Name and
Activecell.Name get results. Any Ideas?

 

[Sharad]

Oh OK, you didn't say you wanted to do it in VBE
Anyway, try following code.

Sub chartPix()
Dim chartShp As Shape, chartName As String, shtName As String
Dim exLeft As Single, exTop As Single
shtName = ActiveSheet.Name
chartName = ActiveChart.Name
chartName = Right(chartName, Len(chartName) - Len(shtName))
chartName = Trim(chartName)
Set chartShp = ActiveSheet.Shapes(chartName)
exLeft = chartShp.Left
exTop = chartShp.Top
chartShp.CopyPicture xlScreen
chartShp.Delete
ActiveSheet.Paste
Selection.Left = exLeft
Selection.Top = exTop

End Sub

If there is no any chart in active sheet or selection is not a chart
then it will give error. I did not add error handling part.

Sharad

 

[Guest]

Hi Sharad,
Most excellant. Thank you for your help. "chartShp.CopyPicture xlScreen" was
completely new to me. In fact when I tried to look it up, I can not find it.
Again, thank you.

 

[Jon Peltier]

David -

Here's a discussion of several methods:

http://peltiertech.com/Excel/ChartsHowTo/DelinkChartData.html

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

 

[Guest]

Thanks Jon - I will go take a look.

David -

Here's a discussion of several methods:

http://peltiertech.com/Excel/ChartsHowTo/DelinkChartData.html

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