Insert a chart on another sheet

[Guest]

I have a macro that copies data from one workbook and then creates a new
workbook with a two data sheets and then I have sheets for the various
charts. When I go to create the charts the charts always end up on the first
sheet in my workbook. Before I create the chart I do the following to goto
the correct sheet:

Sheets("Cond On Temp").Select

Then I have the following:
Charts.Add
ActiveChart.ChartType = xlXYScatterSmooth
ActiveChart.SetSourceData Source:=Sheets("Cond On Temp").Range("A1")
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(1).XValues = "=DataTable!R2C1:R26C1"
ActiveChart.SeriesCollection(1).Values = "=DataTable!R2C3:R26C3"
ActiveChart.SeriesCollection(1).Name = "=DataTable!R1C3"
ActiveChart.Location Where:=xlLocationAsObject, Name:="Cond On Temp"
ActiveChart.HasLegend = True
ActiveChart.Legend.Select
Selection.Position = xlBottom

Cond on Temp is a normal Excel sheet. Why is my chart not being placed on
the correct Excel sheet?

Thanks

 

[Guest]

The method Charts.Add will always add a new chart sheet to the workbook. A
chart that is embedded on another sheet is actually in a "container" called a
chartobject. You need to create the chartobject instead of using Charts.Add.
You can create one this way (you need to specify the location and size using
Left, Top, Width and Height):
Dim MyChartObj as ChartObject, MyChart as Chart
Set MyChartObj = Sheets("Cond On Temp").ChartObjects.Add (Left, Top, Width,
Height)
Set MyChart = MyChartObj.Chart
With MyChart
.ChartType = xlXYScatterSmooth
.SetSourceData Source:=Sheets("Cond On Temp").Range("A1")
...etc
End With

 

[Guest]

Thanks, that worked.

The method Charts.Add will always add a new chart sheet to the workbook. A
chart that is embedded on another sheet is actually in a "container" called a
chartobject. You need to create the chartobject instead of using Charts.Add.
You can create one this way (you need to specify the location and size using
Left, Top, Width and Height):
Dim MyChartObj as ChartObject, MyChart as Chart
Set MyChartObj = Sheets("Cond On Temp").ChartObjects.Add (Left, Top, Width,
Height)
Set MyChart = MyChartObj.Chart
With MyChart
.ChartType = xlXYScatterSmooth
.SetSourceData Source:=Sheets("Cond On Temp").Range("A1")
...etc
End With