Hi, guys,

Would you please help?

I have a stacked bar pivot chart, which only displays the values greater than 0, but I don't want any labels from values equal to 0 showing up and making the chart basically unreadable. I just need the labels from values greater than 0, but getting them automatically with VBA code (macro).

The way I´m doing this is referencing the seriescollection "points" which need to have labels applied. In another worksheet ("referencia") I basically copy the information from the pivot table from all 31 data fiels. Then, range D3:D33 contains the data field number if the value is greater than 0.

Here's my code:

Sub datalabels1()

Dim c
Dim s As Variant

For Each c In Worksheets("referencia").Range("D3:D33").Cells

' If the value in the cell is greater than 0, then variable s gets such value

        If c.Value > 0 Then s = c.Value
    
' Open the worksheet and chart
    Worksheets("Paretos QCPC Dia&Sem").Activate
    ActiveSheet.ChartObjects("Chart 14").Activate
    
' Apply data label to value, since it was greater then 0
    ActiveChart.SeriesCollection(s).ApplyDataLabels AutoText:=True, LegendKey:= _
        False, ShowSeriesName:=True, ShowCategoryName:=False, ShowValue:=True, _
        ShowPercentage:=False, ShowBubbleSize:=False, Separator:=" "

Next

End Sub
When I run this, I get runtime error '1004': Method 'SeriesCollection' of object '_Chart' failed. I think it has to do with my using the s variable on:
ActiveChart.SeriesCollection(s).ApplyDataLabels
, since when I change it to a number, it works fine.

I hope I made sense, but it's late here and I'm desperate.

Thank you,
José