Refreshing dynamic secondary axis

[tourgis2000]

Hi,

I am using Jon Peltier's code to refresh a dynamic chart range as follows:

Sub UpdateChartSourceData()
  With ActiveSheet
    .ChartObjects(1).Chart.SetSourceData _
        Source:=.Range("ChtSourceData"), _
        PlotBy:=xlColumns
  End With
End Sub

The problem is that I have a secondary axis with a dynamic range that I also need to refresh and I can't find a way to reference it through the seriescollection function. It is not as simple as identifying it as seriescollection(x) by using the macro recorder, because the range on the primary axis is a dynamic range and so I won't know the number. Can anyone help?

Thanks in advance,

Martin

 

[tourgis2000]

Thinking further about this I realise that I need to programmatically find the last number in the SeriesCollection and then create the secondary axis from that number +1, something like this:

Dim m As Integer
mymaxseries=20
For m = 1 To ActiveChart.SeriesCollection.Count etc.

... but I am completely hopeless at VBA. Can someone help me to evaluate the last number? I will then need to refresh the source to the primary axis and then add something like:

ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(m+1).Name = "=""Title"""
ActiveChart.SeriesCollection(m+1).Values = "=DynamicRangeName"

I hope this makes sense; if I can just get this part sorted out I can finish this project!

Thanks

Martin