Creating Chart Dynamically (It is Driving Me Nuts)

[Ardy]

OK, I admit I am not a programmer, but god knows I am trying. I am
trying to create a chart via a command button and it works but every
time I activate it, it gives me the same dam chart.
I change some data point and re activate same chart again and it drops
the series name also, can some body give me some insight. I have
formulated the code below with macros that I cleaned up.
------------------------------------------------
Sub ConceptPrintChart()
' Create Chart Concepts About Print
' Ardy
' 12/12/2006

ClearCharts
Application.ScreenUpdating = False
Charts.Add
ActiveChart.Location Where:=xlLocationAsObject, Name:="Roster"
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(1).XValues = "=Roster!R2C1:R43C1"
ActiveChart.SeriesCollection(1).Values = "=Roster!R2C3:R43C3"
ActiveChart.SeriesCollection(1).Name = "=Roster!R1C3"
ActiveChart.SeriesCollection(2).Values = "=Roster!R2C4:R43C4"
ActiveChart.SeriesCollection(2).Name = "=Roster!R1C4"
With ActiveChart
.SetSourceData Range("A243")
.HasTitle = True
.ChartType = xlColumnClustered
.HasLegend = False
.ApplyDataLabels Type:=xlDataLabelsShowValue
.Axes(xlCategory).TickLabels.Orientation = xlHorizontal
.ChartTitle.Font.Bold = True
.ChartTitle.Font.Size = 12
.PlotArea.Top = 18
.PlotArea.Height = 162
.Axes(xlValue).MaximumScale = 0.6
.Deselect'End With
.HasTitle = True
.ChartTitle.Characters.Text = "Title Here"
.Axes(xlCategory, xlPrimary).HasTitle = False
.Axes(xlValue, xlPrimary).HasTitle = False
End With
ActiveChart.HasLegend = True
ActiveChart.Legend.Select
Selection.Position = xlBottom
ActiveChart.HasDataTable = False
ActiveChart.Axes(xlCategory).Select
Selection.TickLabels.AutoScaleFont = True
With Selection.TickLabels.Font
.Name = "Arial"
.FontStyle = "Regular"
.Size = 7
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
.Background = xlAutomatic
End With
ActiveChart.Axes(xlValue).Select
Selection.TickLabels.AutoScaleFont = True
With Selection.TickLabels.Font
.Name = "Arial"
.FontStyle = "Regular"
.Size = 7
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
.Background = xlAutomatic
End With
ResizeChart
Application.ScreenUpdating = True
Range("C1").Select
Application.ScreenUpdating = True
End Sub

 

[Spreadsheet Solutions]

Ardy;

Try creating a dynamic chart this way:
http://www.rosenkrantz.nl/charttip03.htm

 

[Ardy]

Thanks.....
I incorporated some of codes from your link....Intreasting. I finally
got the dam thing to work.....

Ardy