dnicholsby
New Member
- Joined
- Jan 24, 2017
- Messages
- 26
Hi all,
I've created a macro to select the series for two different charts. For some reason every time i run it it adds a two other random blank series and the formatting for all my series changes each time. (Sometimes won't show in the legend and other times colours of the series will change). I can't understand why, here is my code:
I've created a macro to select the series for two different charts. For some reason every time i run it it adds a two other random blank series and the formatting for all my series changes each time. (Sometimes won't show in the legend and other times colours of the series will change). I can't understand why, here is my code:
Code:
Sub CountryChart()
'This macro updates the country chart
Dim y As Integer
Dim x As Integer
x = range("K1", range("K1").End(xlDown)).Rows.Count
y = range("T1", range("T1").End(xlDown)).Rows.Count
ActiveSheet.ChartObjects("Chart 3").Activate
On Error Resume Next
ActiveChart.FullSeriesCollection(1).Delete
ActiveChart.FullSeriesCollection(2).Delete
ActiveChart.FullSeriesCollection(3).Delete
ActiveChart.SeriesCollection.NewSeries
ActiveChart.FullSeriesCollection(1).Name = "=Summary!$M$1"
ActiveChart.FullSeriesCollection(1).Values = "=Summary!$M$2:$M$" & x
ActiveChart.FullSeriesCollection(1).XValues = "=Summary!$K$2:$K" & x
ActiveChart.SeriesCollection.NewSeries
ActiveChart.FullSeriesCollection(2).Name = "=Summary!$P$1"
ActiveChart.FullSeriesCollection(2).Values = "=Summary!$P$2:$P$" & x
ActiveChart.FullSeriesCollection(2).XValues = "=Summary!$K$2:$K" & x
ActiveChart.SeriesCollection.NewSeries
ActiveChart.FullSeriesCollection(3).Name = "=Summary!$Q$1"
ActiveChart.FullSeriesCollection(3).Values = "=Summary!$Q$2:$Q$" & x
ActiveChart.FullSeriesCollection(3).XValues = "=Summary!$K$2:$K" & x
ActiveSheet.ChartObjects("Chart 2").Activate
On Error Resume Next
ActiveChart.FullSeriesCollection(1).Delete
ActiveChart.FullSeriesCollection(2).Delete
ActiveChart.FullSeriesCollection(3).Delete
ActiveChart.SeriesCollection.NewSeries
ActiveChart.FullSeriesCollection(1).Name = "=Summary!$W$1"
ActiveChart.FullSeriesCollection(1).Values = "=Summary!$W$2:$W$" & y
ActiveChart.FullSeriesCollection(1).XValues = "=Summary!$U$2:$U" & y
ActiveChart.SeriesCollection.NewSeries
ActiveChart.FullSeriesCollection(2).Name = "=Summary!$Z$1"
ActiveChart.FullSeriesCollection(2).Values = "=Summary!$Z$2:$Z$" & y
ActiveChart.FullSeriesCollection(2).XValues = "=Summary!$U$2:$U" & y
ActiveChart.SeriesCollection.NewSeries
ActiveChart.FullSeriesCollection(3).Name = "=Summary!$AA$1"
ActiveChart.FullSeriesCollection(3).Values = "=Summary!$AA$2:$AA$" & y
ActiveChart.FullSeriesCollection(3).XValues = "=Summary!$U$2:$U" & y
End Sub