I have macros in 2003, however they do not work in 2007. So I recorded a new
macro in Excel 2007 which inserts a chart into the active sheet. Works great
when you record it, however when you go to use the macro it hangs up on the
first line. Part of the macro is as follows:
ActiveSheet.Shapes.addchart.Select
ActiveChart.ChartType = xlLineMarkers
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(1).Values = "='52weeks'!$C$2:$C$54"
ActiveChart.SeriesCollection(1).Name = "='52weeks'!$C$1"
ActiveChart.SeriesCollection(1).XValues = "='52weeks'!$B$2:$B$54"
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(2).Values = "='52weeks'!$D$2:$D$54"
ActiveChart.SeriesCollection(2).Name = "='52weeks'!$D$1"
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(3).Values = "='52weeks'!$E$2:$E$54"
ActiveChart.SeriesCollection(3).Name = "='52weeks'!$E$1"

The macro that is built in 2003 works great including the charting aspect.
In 2007 everything works except the charting. I have tried various ways of
inserting a chart in 2007 with a macro, but none of them work. Any ideas
would be greatly appreciated. Remember though that 2003 and 2007 are quite
different when it comes to charting and I'm getting the feeling that the when
it comes to recording the code it does not work the way it should.

PCF_man

"PCF_man" wrote:

I have macros in 2003, however they do not work in 2007. So I recorded a new
macro in Excel 2007 which inserts a chart into the active sheet. Works great
when you record it, however when you go to use the macro it hangs up on the
first line. Part of the macro is as follows:
ActiveSheet.Shapes.addchart.Select
ActiveChart.ChartType = xlLineMarkers
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(1).Values = "='52weeks'!$C$2:$C$54"
ActiveChart.SeriesCollection(1).Name = "='52weeks'!$C$1"
ActiveChart.SeriesCollection(1).XValues = "='52weeks'!$B$2:$B$54"
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(2).Values = "='52weeks'!$D$2:$D$54"
ActiveChart.SeriesCollection(2).Name = "='52weeks'!$D$1"
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(3).Values = "='52weeks'!$E$2:$E$54"
ActiveChart.SeriesCollection(3).Name = "='52weeks'!$E$1"

The macro that is built in 2003 works great including the charting aspect.
In 2007 everything works except the charting. I have tried various ways of
inserting a chart in 2007 with a macro, but none of them work. Any ideas
would be greatly appreciated. Remember though that 2003 and 2007 are quite
different when it comes to charting and I'm getting the feeling that the when
it comes to recording the code it does not work the way it should.

PCF_man


Worked this out myself.

The problem is that Excel 2007 charting model works completely different
than in 2003. A simple macro as follows solved the problem

Sub addchart()
Dim WS As Worksheet
Dim Rng As Range
Set Rng = Range("A65")
Set WS = Worksheets("52weeks")
WS.Shapes.addchart(xlLineMarkers, _
Left:=WS.Range("M2").Left, _
Top:=WS.Range("M2").Top, _
Width:=WS.Range("M2:T2").Width, _
Height:=WS.Range("M2:M17").Height).Select
ActiveChart.SetSourceData Source:=Range("C2:E54")
ActiveChart.SeriesCollection(1).XValues = "=52weeks!$A$2:$A$54"
ActiveChart.SeriesCollection(1).Name = "=52weeks!$C$1"
ActiveChart.SeriesCollection(2).Name = "=52weeks!$D$1"
ActiveChart.SeriesCollection(3).Name = "=52weeks!$E$1"
ActiveChart.ApplyChartTemplate ("2007_52weeks")
ActiveChart.SetElement msoElementChartTitleAboveChart
ActiveChart.ChartTitle.Caption = Rng
End Sub