Defining graph series using VBA

[Andrew Entee]

I have code that creates a graph from dynamic data. To do this I’m using a pre-created graph in a workbook that uses series in another worksheet in the same workbook. To change the graph – I just put new data in the worksheet cells that house the series data. The code works fine.

This is a data sample
junk1.png

The challenge is I don’t know how to change the series through VBA so I’ve manually plugged in an end row value of $30.
Junk2.jpg

This creates a bunch of blanks in the graph when the data is less than 30 rows long.
How can I set the correct series length with VBA code in the code below?

Range1 = TimeStampCol & "1," & TotalCol & "1," & IPercentileCol & "1," & AllPercentileCol & "1" ' 
RangeC = Range1 & "," & TimeStampCol & StartRow & ":" & TimeStampCol & Row & "," & TotalCol & StartRow & ":" & TotalCol & Row & "," & IPercentileCol & StartRow & ":" & IPercentileCol & Row & "," & AllPercentileCol & StartRow & ":" & AllPercentileCol & Row
WB.Activate                                  
WB.Sheets(GraphData).Range("A2:E99").ClearContents
WB.Sheets(GraphDataSource).Activate
WB.Sheets(GraphDataSource).Cells(1, 1).Select
WB.Sheets(GraphDataSource).Range(RangeC).Select
WB.Sheets(GraphDataSource).Range(RangeC).Copy WB.Sheets(GraphData).Cells(1, 1)   from
WB.Sheets(ChartTemplateName).Copy after:=WB.Sheets(Sheets.Count)             ' Now copy the Chart over from the template
ActiveSheet.Name = ChartName
WB.Sheets(TrendChartName).Select