VBA to simplify a pivotchart legend?

[saotome1]

Hello, I've included two fields into my pivotchart legend resulting in hundreds of unique legend entries. (I need the two fields here to keep my data charted in the correct order). So I've essentially got one field being ascending numbers, and the other being the data I actually care about, e.g. as below.

I think I need to use VBA to simplify this.

Sub SimplifyLegend()
Dim chart As Chart
Dim series As Series

' Set the chart object
Set cht = ActiveSheet.ChartObjects(1).Chart

' Loop through each series in the chart
For Each ser In cht.SeriesCollection
' Check if the series name contains "sand"
If InStr(ser.Name, "sand") > 0 Then
ser.Name = "sand"
' Check if the series name contains "clay"
ElseIf InStr(ser.Name, "clay") > 0 Then
ser.Name = "clay"
End If
Next ser
End Sub

 

[saotome1]

Hello, I've included two fields into my pivotchart legend resulting in hundreds of unique legend entries. (I need the two fields here to keep my data charted in the correct order). So I've essentially got one field being ascending numbers, and the other being the data I actually care about, e.g. as below.

View attachment 115476
I think I need to use VBA to simplify this.

error with the dimension in my code there (should say cht/ser). Well this code didn't do anything. Does anybody have any ideas?