Charts in copied worksheets

S

Shawn

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
 
T

Tom Ogilvy

Why not make the chart a picture?

--
Regards,
Tom Ogilvy


Shawn said:
Hi

I'm attempting to copy a worksheet from one workboo to antoher and than
Click to expand...
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.
Click to expand...
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
Click to expand...
appearance unchanged at the same time (almost like treating charts as
pictures)?
 
S

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
 
J

Jon Peltier

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/
_______
 
S

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
 
Top