Chart Source when copying sheets to new file.

[Keithlo]

I am using VBA to copy select sheets to a new file and saving that new file.
Some sheets have charts, others the source for those charts. But the charts
in the new file point back to the original file, even though I have also
copied the source sheets into the new file with the same command using this
code:

Dim mySheet As Worksheet
Dim sheetList As Variant

ReDim sheetList(1 To ActiveWorkbook.Sheets.Count)
i = 0 'initialize variable
For Each mySheet In ActiveWorkbook.Sheets
If mySheet.Name <> "CHARTS (2)" And mySheet.Name <> "TABLE" Then
i = i + 1
sheetList(i) = mySheet.Name
End If
Next
ReDim Preserve sheetList(1 To i)
Sheets(sheetList).Copy

Is there any way to make the new charts get their source from the new source
sheets, or do I have to use SaveCopyAs and save the entire file instead?

Thanks,

Keithlo

 

[ryguy7272]

As far as I know, you can't do what you are trying to do.

You can do all kinds of things like pasting link (and of course the chart)
into PowerPoint or Word, and then when you update the Excel data, the chart
is updated appropriately. That's very different from what you are describing
though.

Again you can't do what you described unless maybe you do some operations on
the active Excel file (delete Sheets, or whatever), save the file and rename
the file. That should be essentially the same thing as copy/paste, right.

This is a good site for tons of chart-related info.
http://www.contextures.com/charts.html

Regards,
Ryan---

 

[Keithlo]

Ok. Thanks for the reply and link.

Keithlo

As far as I know, you can't do what you are trying to do.

You can do all kinds of things like pasting link (and of course the chart)
into PowerPoint or Word, and then when you update the Excel data, the chart
is updated appropriately. That's very different from what you are describing
though.

Again you can't do what you described unless maybe you do some operations on
the active Excel file (delete Sheets, or whatever), save the file and rename
the file. That should be essentially the same thing as copy/paste, right.

This is a good site for tons of chart-related info.
http://www.contextures.com/charts.html

Regards,
Ryan---