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

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---

--
RyGuy


"Keithlo" wrote:

> 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

Ok. Thanks for the reply and link.

Keithlo

"ryguy7272" wrote:

> 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---
>
> --
> RyGuy
>
>
> "Keithlo" wrote:
>
> > 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