Hello,
I have some datas on the sheet "Graphics" which I want to plot in the SAME Chart. But my code creates a new Chart for the new row by deletting the current Chart. How can I plot all my datas in the same Chart?
Function CreateLineCharts() As Boolean
Dim myChtObj As ChartObject
Dim rngChtData As Range
Dim rngChtXVal As Range
Dim iColumn As Long
Dim sheetName As String
sheetName = "DataSource"
Dim WSD As Worksheet
Set WSD = Worksheets("Graphics")
Dim chartSheet As String
chartSheet = "ChartOutput"
Dim CSD As Worksheet
Set CSD = Worksheets("Table3")
'get the current charts so proper overwriting can happen
Dim chtObjs As ChartObjects
Set chtObjs = CSD.ChartObjects
'turn off autofilter mode
WSD.AutoFilterMode = False
'Find the last row with data
Dim finalRow As Long
Dim i As Integer
finalRow = WSD.Cells(Application.Rows.Count, 2).End(xlUp).row
'add the chart
Charts.Add
With ActiveChart
'to determine how many values to loop over, find the last row in the data set
For i = 1 To finalRow
Dim chartName As String
chartName = WSD.Cells(i, 1).Value
'Delete Chart if it already exists, make a new one
Dim chtObj As ChartObject
For Each chtObj In chtObjs
If chtObj.Name = chartName Then
chtObj.Delete
End If
Next
'define chart data range for the row (record)
Dim dataString As String
dataString = "B" & i & ":P" & i
Set rngChtData = WSD.Range(dataString)
'define the x axis values
Set rngChtXVal = WSD.Range("$B$1:$P$1")
'make a Line chart
.ChartType = xlLineStacked
'remove extra series
Do Until .SeriesCollection.Count = 0
.SeriesCollection(1).Delete
Loop
'add series from selected range, column by column
With .SeriesCollection.NewSeries
.Values = rngChtData
.XValues = rngChtXVal
.Name = "XX"
End With
Next i
End With
End Function