VBA SeriesCollection Help! Excel 2010, apply data labels to all series

[ntgarwood]

I am trying to apply a formatted data label to all series in a pivot chart. I can do it if i name the series as noted below, but haven't been able to find a loop or a command to apply to all the series in the chart.

Here's what I have for applying to a single series, which works:

ThisWorkbook.Worksheets("WaterFall TPut").ChartObjects("waterfall throughput").Activate
ActiveChart.SeriesCollection(1).Select
ActiveChart.SeriesCollection(1).ApplyDataLabels
ActiveChart.SeriesCollection(1).DataLabels.ShowValue = True
ActiveChart.SeriesCollection(1).DataLabels.Select
Selection.NumberFormat = "0%;-0%;"

And here was my attempt at looping something similar, which I've played around with endlessly with no success:

Public Sub LoopThroughSeries()
Dim myChart As ChartObject
Dim mySeries As Series

With myChart
For Each mySeries In myChart.Chart.SeriesCollection
ActiveChart.SeriesCollection().ApplyDataLabels
ActiveChart.SeriesCollection().DataLabels.ShowValue = True
ActiveChart.SeriesCollection().DataLabels.Select
Selection.NumberFormat = "0%;-0%;"
Next
End With

End Sub

PLEASE HELP! I would think this would be simple, and with "FullSeriesCollection" post 2010 i think it would be.

 

[Jon Peltier]

Use code tags: open square bracket + CODE + close square bracket, then your code, then open square bracket + /CODE + close square bracket
Or use the hash button above the edit box.

Try this:

With myChart
  For Each mySeries In myChart.Chart.SeriesCollection
    With mySeries
      .ApplyDataLabels
      .DataLabels.ShowValue = True
      .DataLabels.NumberFormat = "0%;-0%;"
    End With
  Next
End With

 

[ntgarwood]

Thanks Jon!! Also, apologies for my rookie mistake on posting the code.

After using your recommended code and applying some other pieces along with it, i found a solution that works perfectly!! Here is the final piece of code which i used on 2 different worksheets, showing only 1 to keep it shorter:

ThisWorkbook.Worksheets("WaterFall TPut").ChartObjects("waterfall throughput").Activate
Dim wb As Workbook
Dim ws As Worksheet
Dim mychart As ChartObject
Dim myseries As Series
Set wb = ThisWorkbook
Set ws = wb.Sheets("WaterFall Tput")
Set mychart = ws.ChartObjects("waterfall throughput")
  For Each myseries In mychart.Chart.SeriesCollection
    With myseries
       .ApplyDataLabels
       .DataLabels.ShowValue = True
       .DataLabels.NumberFormat = "0%;-0%;"
    End With
  Next

Would love to know if you have any recommendations or comments on the final solution, but this did end up working! Thank you so much, myself and my job appreciate it.