Hi Fredrik E. Nilsen;

You need to put the chart into a different workbook. It's blanking out
because Excel automatically links the data. When you put it in a seperate
workbook if I undersand you correctly it will solve your problem

Thanks,
--
DownThePaint
Where points are scored and games are won!


"Fredrik E. Nilsen" wrote:

Hi again,

I'm trying to use VBA to:

- copy a chart in a chart sheet
- paste it as an embedded chart in a new sheet
- resize (and format) the chart
- copy the embedded chart to the clipboard
- delete the new sheet

This is the code I've come up with so far:

Sub CopyChart()
ActiveChart.ChartArea.Select
ActiveChart.ChartArea.Copy
ActiveWorkbook.Sheets.Add
ActiveSheet.Paste
ActiveSheet.ChartObjects("Chart 1").Activate
With ActiveChart.Parent
.Height = 252.75
.Width = 342.75
ActiveChart.PlotArea.Height = 205
ActiveChart.PlotArea.Width = 340
ActiveChart.ChartArea.Copy
Application.DisplayAlerts = False
ActiveSheet.Delete
Application.DisplayAlerts = True
End With
End Sub

The problem is: when the ActiveSheet is deleted, the copied chart is
no longer available on the clipboard. I can use the CopyPicture method
in stead, but then there will be a different size when pasted in
another application.

--
Fredrik E. Nilsen