Add series automatically to a graph

[Renah]

Hi,

I'm wondering if you can help me.
I've got a graph which feeds from a worksheet with a varying number of rows. I need a VBA code to insert a new series into the graph whenever a new row is inserted into the table on the worksheet.

My initial thoughts were to name each row SeriesData(i) where i spans the number of rows in the table. If I could do this then I would know how to add the series. However, I can't work out how to name a range SeriesData(i) where i is a variable.

Any help would be very gratefully received!
I've trawled the internet and can't seem to find anything on this issue!

Kind Regards,

Renah

 

[Derek Brown]

Hello and welcome to The Board.
Look at Jon's excellent site:
http://peltiertech.com/Excel/Charts/ChartIndex.html
and you will find many chart examples and files to download.
Look under the index section "Dynamic Charts".

 

[Renah]

Hi,

Thank you for your help, I actually managed to solve the problem by myself!
But thank you for your reply

 

[GsJosh]

Hi,

Thank you for your help, I actually managed to solve the problem by myself!
But thank you for your reply

It is extremely frustrating reading that you fixed the problem, but without telling how.

Please, Renah, tell me how you solved it so I can stop looking through the whole internet for a solution!

 

[Renah]

It is extremely frustrating reading that you fixed the problem, but without telling how.

Please, Renah, tell me how you solved it so I can stop looking through the whole internet for a solution!

Hi,

Apologies for not posting the answer! I have copied in the code that I wrote below - although please be aware that I am by no means a good code writer so there's probably a lot of improvements to be made! I also wrote the code in 2011 and haven't looked at it since!

The code was intended to update a separate graph for each of 5 sheets automatically, to match the graph range with the number of rows in a data set.

I hope this helps.

Code:


Sub GraphSeries()</SPAN>

Dim xAxisVal As String</SPAN>
Dim SeriesData As Range</SPAN>
Dim SeriesTitle As Range</SPAN>


' Name ranges on each summary sheet for the graph</SPAN>

ActiveWorkbook.Names("Range1").DELETE</SPAN>
ActiveWorkbook.Names("Range2").DELETE</SPAN>
ActiveWorkbook.Names("Range3").DELETE</SPAN>
ActiveWorkbook.Names("Range4").DELETE</SPAN>
ActiveWorkbook.Names("Range5").DELETE</SPAN>

Sheets("Sheet1").Select</SPAN>
Range("B4").Select</SPAN>
Range(Selection, Selection.End(xlDown).Offset(-4, 0)).Select</SPAN>
Range("B4", Range("B4").End(xlDown).Offset(-4, 0)).Name = "Range1"</SPAN>
Range("A1").Select</SPAN>

Sheets("Sheet2").Select</SPAN>
Range("B4").Select</SPAN>
Range(Selection, Selection.End(xlDown).Offset(-4, 0)).Select</SPAN>
Range("B4", Range("B4").End(xlDown).Offset(-4, 0)).Name = "Range2"</SPAN>
Range("A1").Select</SPAN>

Sheets("Sheet3").Select</SPAN>
Range("B4").Select</SPAN>
Range(Selection, Selection.End(xlDown).Offset(-4, 0)).Select</SPAN>
Range("B4", Range("B4").End(xlDown).Offset(-4, 0)).Name = "Range3"</SPAN>
Range("A1").Select</SPAN>

Sheets("Sheet4").Select</SPAN>
Range("B4").Select</SPAN>
Range(Selection, Selection.End(xlDown).Offset(-4, 0)).Select</SPAN>
Range("B4", Range("B4").End(xlDown).Offset(-4, 0)).Name = "Range4"</SPAN>
Range("A1").Select</SPAN>

Sheets("Sheet5").Select</SPAN>
Range("B4").Select</SPAN>
Range(Selection, Selection.End(xlDown).Offset(-4, 0)).Select</SPAN>
Range("B4", Range("B4").End(xlDown).Offset(-4, 0)).Name = "Range5"</SPAN>
Range("A1").Select</SPAN>


' Set the series for the graphs</SPAN>

Call SetSeries("Sheet1", "Sheet1 Chart", "Range1", "xAxisVal1", "SeriesData1")</SPAN>
Call SetSeries("Sheet2", "Sheet2 Chart", "Range2", "xAxisVal2", "SeriesData2")</SPAN>
Call SetSeries("Sheet3", "Sheet3 Chart", "Range3", "xAxisVal3", "SeriesData3")</SPAN>
Call SetSeries("Sheet4", "Sheet4 Chart", "Range4", "xAxisVal4", "SeriesData4")</SPAN>
Call SetSeries("Sheet5", "Sheet5 Chart", "Range5", "xAxisVal5", "SeriesData5")</SPAN>

Sheets("Sheet1").Select</SPAN>

MsgBox ("The macro has finished running")</SPAN>

End Sub</SPAN>


Sub SetSeries(tn As String, cn As String, rn As String, xvn As String, sd As String)</SPAN>

' Set the series for the graph for tab tn</SPAN>

' tn = tab name, cn = chart name, rn = range name, xvn = x axis values name, sd = series data name, st = series title name</SPAN>

Dim st As String</SPAN>

Sheets(tn).Select</SPAN>
Range("D3").Select</SPAN>
Range("D3", Range("D3").End(xlToRight)).Name = xvn</SPAN>

Sheets(cn).Activate</SPAN>
N = ActiveChart.SeriesCollection.Count</SPAN>
If N > Range(rn).Rows.Count Then</SPAN>
' Add bar chart series</SPAN>
For k = 1 To Range(rn).Rows.Count</SPAN>
st = Sheets(tn).Cells(k + 3, "B").Value</SPAN>
Range(Sheets(tn).Cells(k + 3, "D"), Sheets(tn).Cells(k + 3, "D").End(xlToRight)).Name = sd</SPAN>
With ActiveChart.SeriesCollection(k)</SPAN>
.Values = Range(sd)</SPAN>
.XValues = Range(xvn)</SPAN>
.Name = st</SPAN>
End With</SPAN>
Next k</SPAN>
' Delete any remaining series from old graph</SPAN>
j = Range(rn).Rows.Count + 1</SPAN>
For i = j To N</SPAN>
ActiveChart.SeriesCollection(j).DELETE</SPAN>
Next i</SPAN>
End With</SPAN>

ElseIf N = Range(rn).Rows.Count Then</SPAN>
' Add bar chart series</SPAN>
For k = 1 To Range(rn).Rows.Count</SPAN>
st = Sheets(tn).Cells(k + 3, "B").Value</SPAN>
Range(Sheets(tn).Cells(k + 3, "D"), Sheets(tn).Cells(k + 3, "D").End(xlToRight)).Name = sd</SPAN>
With ActiveChart.SeriesCollection(k)</SPAN>
.Values = Range(sd)</SPAN>
.XValues = Range(xvn)</SPAN>
.Name = st</SPAN>
End With</SPAN>
Next k</SPAN>
End With</SPAN>

ElseIf N < Range(rn).Rows.Count Then</SPAN>
' Add bar chart series</SPAN>
For k = 1 To N</SPAN>
st = Sheets(tn).Cells(k + 3, "B").Value</SPAN>
Range(Sheets(tn).Cells(k + 3, "D"), Sheets(tn).Cells(k + 3, "D").End(xlToRight)).Name = sd</SPAN>
With ActiveChart.SeriesCollection(k)</SPAN>
.Values = Range(sd)</SPAN>
.XValues = Range(xvn)</SPAN>
.Name = st</SPAN>
End With</SPAN>
Next k</SPAN>
' Add additional series for rows from Range1</SPAN>
For k = N + 1 To Range(rn).Rows.Count</SPAN>
st = Sheets(tn).Cells(k + 3, "B").Value</SPAN>
Range(Sheets(tn).Cells(k + 3, "D"), Sheets(tn).Cells(k + 3, "D").End(xlToRight)).Name = sd</SPAN>
With ActiveChart.SeriesCollection.NewSeries</SPAN>
.Values = Range(sd)</SPAN>
.XValues = Range(xvn)</SPAN>
.Name = st</SPAN>
End With</SPAN>
Next k</SPAN>
End With</SPAN>

End If</SPAN>


End Sub</SPAN>