H

I'm attempting to copy a worksheet from one workboo to antoher and than have the data as values in the new workbook including charts. I can us

With ActiveSheet.UsedRang
.Value = .Valu
End Wit

to turn all cells into values. The problem is with chart data sources. They stay as links to the source workbook and every time I open the new workbook it asks for link refresh

Is there a way of eliminating this programmatically and keeping chart appearance unchanged at the same time (almost like treating charts as pictures)

Thank

Shawn

Why not make the chart a picture?

--
Regards,
Tom Ogilvy


"Shawn" wrote in message
...
Hi

I'm attempting to copy a worksheet from one workboo to antoher and than
have the data as values in the new workbook including charts. I can use

With ActiveSheet.UsedRange
.Value = .Value
End With

to turn all cells into values. The problem is with chart data sources.
They stay as links to the source workbook and every time I open the new
workbook it asks for link refresh.

Is there a way of eliminating this programmatically and keeping chart
appearance unchanged at the same time (almost like treating charts as
pictures)?

Thanks

Shawn

Hi To

I need to keep the placement exact in the workbook and worksheet I'm copying charts to

I do have code that uses Top and Left properties of the chart object and copyPicture method of the chart object to get this done but it ends up being rather complex. I was wondering if there is simpler way

Thank

Shawn

Shawn -

Are you copying a chart to a worksheet in another workbook? The chart
still points back to the sheet it came from. If the data is arranged the
same (same rows and columns, labels in the same place), copy the entire
sheet that contains the chart (so you have two sheets), then copy the
data from the new workbook, and use Paste Special - Values to get it
onto the sheet under the chart. The chart will update according to the
new data.

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

Shawn wrote:
Hi

I'm attempting to copy a worksheet from one workboo to antoher and
than have the data as values in the new workbook including charts. I
can use

With ActiveSheet.UsedRange .Value = .Value End With

to turn all cells into values. The problem is with chart data
sources. They stay as links to the source workbook and every time I
open the new workbook it asks for link refresh.

Is there a way of eliminating this programmatically and keeping chart
appearance unchanged at the same time (almost like treating charts as
pictures)?

Thanks

Shawn

H

First of all thanks for all the help

What I'm attempting to do is gather worksheets from various workbooks into one final workbook. What Jon suggested would work fine if the data is on the same worksheet as the chart or if I want it to be at the end. The problem is that I don't want it to be because in the final workbook each worksheet ends up being a page of a large report. This is a final stage of my Excel reporting engine project

By the sounds of it looks like chart.CopyPicture approach and than paste and reposition in the new workbook is the only way

Thank

Shawn