Help needed to add a Chart with a macro in Excel 2007

**Clemens** · 10-04-2009

Hi friends,

I have already added some data in a sheet for which i have built a macro. But now i want same data to showed through chart.For the same i have made a recording for macro to chart it and it is included below. But when I try to run the recorded macro it hangs up on the "ActiveSheet.Shapes.addchart.Select" .
Can any one help me out with this issue.

Code:

Range("B1:E54").Select
ActiveSheet.Shapes.addchart.Select
ActiveChart.SetSourceData Source:=Range("'52weeks'!$B$1:$E$54")
ActiveChart.ChartType = x1LineMarkers

**Crona** · 10-04-2009

I would prefer you not to record macro instead of that you can take an example of the following code which will help you a lot.

Code:

Sub Chrt_Current_Status_Click()

Dim chtChart As Chart

' Create a new chart.

Set chtChart = Charts.Add
Set chtChart = chtChart.Location(Where:=xlLocationAsObject,
Name:="sheetname")

With chtChart

.ChartType = xlCylinderColClustered ' Chart type

' Set data source range.

.SetSourceData Source:=Sheets("BASIC CHART DATA").Range("G34:H39") '
source of data
.HasTitle = True
.ChartTitle.Text = "Current Status"
.SeriesCollection(1).XValues = "='BASIC CHART DATA'!$G$34:$G$39"
'.Axes(xlCategory, xlPrimary) = True

' The Parent property is used to set properties of the Chart. ' sets
size of chart

With .Parent
.Top = Range("G3").Top
.Left = Range("G3").Left
.Width = Range("G3:R34").Width
.Height = Range("G3:R34").Height

End With

End With

End Sub

**Clark** · 11-04-2009

I had made similar type of issue, I use the following code and it worked for me hope, it,s works for you too.

Code:

Dim myChtObj As ChartObject
Dim rngChtData As Range
Dim rngChtXVal As Range
Dim iColumn As Long

If TypeName(Selection) <> "Range" Then Exit Sub
Range("B1:E54").Select
Set rngChtData = Selection
With rngChtData
Set rngChtXVal = .Columns(1).Offset(1).Resize(.Rows.Count - 1)
End With
Set myChtObj = ActiveSheet.ChartObjects.Add _
(Left:=250, Width:=375, Top:=75, Height:=225)
With myChtObj.Chart
.ChartType = xlLineMarkers

Do Until .SeriesCollection.Count = 0
.SeriesCollection(1).Delete
Loop
For iColumn = 2 To rngChtData.Columns.Count
With .SeriesCollection.NewSeries
.Values = rngChtXVal.Offset(, iColumn - 1)
.XValues = rngChtXVal
.Name = rngChtData(1, iColumn)
End With
Next
myChtObj.Activate
End With
Call FormatChart

**Trader_Joe** · 19-11-2010

I get the same problem as reported by Clemens, so I tried Crona's suggestion and now I get the following error:
Type Mismatch on the Set chtChart = Charts.Add

' Create a new chart.

Set chtChart = chtChart.Location(Where:=xlLocationAsObject,
Name:="sheetname")

Any help please, this is driving me nuts.