Hello! All. I want to save some charts in an Excel file(workbook1) into
a new Excel file(workbook2) and save the new Excel file as a new name
and in a fixed location of some folder. Could anybody give me some
suggestions? BTW, I wish to develop some VBA code to do it
automatically. Since I want to do it within a loop, I prefer there's no
manual intervention. Thanks in advance!

Embedded charts or chart sheets?

To copy all chart sheets in a workbook to a new workbook:

' copy the chart sheets into a new workbook
ActiveWorkbook.Charts.Copy
' save the new workbook
ActiveWorkbook.SaveAs "C:\Temp\ChartBook.xls"
' close the new workbook
ActiveWorkbook.Close False

To copy embedded charts, you have to loop through the sheets and copy
the chartobjects.

Sub CopyChartObjects()

Dim wbSource As Workbook
Dim wbTarget As Workbook
Dim wksht As Worksheet

Set wbSource = ActiveWorkbook
Set wbTarget = Workbooks.Add(xlWBATWorksheet)
wbTarget.Worksheets(1).Name = "To Be Deleted"
For Each wksht In wbSource.Worksheets
If wksht.ChartObjects.Count 0 Then
wbTarget.Worksheets.Add.Name = wksht.Name
wksht.ChartObjects.Copy
wbTarget.Worksheets(wksht.Name).Paste
End If
Next

Application.DisplayAlerts = False
wbTarget.Worksheets("To Be Deleted").Delete
Application.DisplayAlerts = True

End Sub


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


hong wrote:

Hello! All. I want to save some charts in an Excel file(workbook1) into
a new Excel file(workbook2) and save the new Excel file as a new name
and in a fixed location of some folder. Could anybody give me some
suggestions? BTW, I wish to develop some VBA code to do it
automatically. Since I want to do it within a loop, I prefer there's no
manual intervention. Thanks in advance!

Thanks a lot! Jon. In my case, they are chart sheets. I have a Excel
file named " Results1" and there're about three chart-sheets in it. Say
chart1, chart2 and chart3. What I want to do is copy and save those
charts into a new Excel file and name it as" Charts output1" in a
particular folder( or a msgbox asking the user to choose a location).
The reason why I name these two files with a number 1 is because I need
to do a loop to repeat the " copy and save" process to deal with around
100 files. And I wonder when I copy the chartsheets if I also need to
copy the source data of those charts? Thanks again!

The charts remain linked to the original data sheets. If you need to
keep the data with the charts, you are probably better off just copying
the whole workbook, unless you want to write a lot of code to separate
out the data for your charts, change the source data range to this
extracted data, etc.

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


hong wrote:

Thanks a lot! Jon. In my case, they are chart sheets. I have a Excel
file named " Results1" and there're about three chart-sheets in it. Say
chart1, chart2 and chart3. What I want to do is copy and save those
charts into a new Excel file and name it as" Charts output1" in a
particular folder( or a msgbox asking the user to choose a location).
The reason why I name these two files with a number 1 is because I need
to do a loop to repeat the " copy and save" process to deal with around
100 files. And I wonder when I copy the chartsheets if I also need to
copy the source data of those charts? Thanks again!

Thank you again! Jon. As you mentioned, when I copy the charts and save
them into anther new Excel file, those charts are still linked to the
original data sheet. But in my case, I use one Excel file as my code
file, and when I input data from my data file and run the program in
my code file to get some data results and charts, I output these data
results into my result file and save those charts into another new
file. The problem is when I input new data and run the program, those
data results and charts will also be updated and I want them be saved
to a new file too. That means I can not keep the link between the code
file to my charts file. Do you have any idea that if I can break the
link or just copy the source data worksheets with those charts? Thanks!

Could anybody help me out? Thanks!

What's in the report worksheets that you don't want to retain? The
process of going through the charts and parsing what is and isn't source
data is long and tortuous. I stick with my earlier suggestion to save a
copy of the original workbook. Alternatively, make the macro that builds
the report workbook build a slimmed down version with just the data and
charts.

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


hong wrote:

Could anybody help me out? Thanks!