Pivot Chart Formating Issue - Seriescollection Name Designation

Hi,

I’m a dummy for VBA and I need help on VBA code on formatting on pivot chart with 2 axes.

When I want to format the bar (primary axis) and line (secondary axis) on the chart with the macro I recorded, it works fine only when all bars and lines are available. If I change the filter, then I will get an error code.
I think this is due to the seriescollection() selection issue as sometimes the seriescollection number is different for different filter. Another issue is that the seriescollection numbering is also different for my primary and secondary series. For example, if I have 10 series, the 1st 5 (1-5) will be on primary, the last 5 (6-10) will be on secondary. When if I have 6 series, this criteria is different again. (primary 1-3, secondary 4-6). I have to re-format each of those series.

I try to solve this by using “select Case”. However, I have problem with the case name as I have no idea how to set the name for each series. Can anyone tell me where can I find the name for the “Case”?? My pivot table consists of Customer A, B and C for revenue $M (primary axis) and order_line (secondary axis).

Your help is very much appreciated.

Okay. i tried to upload the file but it exceeds 1MB due to all the pivot table and chart. So i try to paste here instead.
My pivot table is kind of like below. you can see there are 2 axis - one being revenue and one being order_line.

Parent Customer C

Data End Customer
Revenue $ ORDER_LINE
Quarter C FF E HH C FF E HH
Q2 15251.6 86299.2 50.00% 50.00%
Q3 3836.9 68632.2 50.00% 25.00%
Q4 33633.6 0.00% 25.00%
Grand Total 19088.5 188565 100.00% 100.00%

since i have a lot of different Parent customer filter, i can't set seriescollection(1)(2) etc..

the code i have is currently as below. But it just won't work. I think there is something wrong with my "Case Name". Maybe i name them wrongly? Can anyone tell what what should i do on this? Thank you.

PHP Code: 

Sub formating()

Dim s As Series

With ActiveChart

    For Each s In .SeriesCollection

        Select Case s.Name
            Case "Revenue $ - E FF"
                 With Selection.Border
                    .Weight = xlThin
                    .LineStyle = xlAutomatic
                 End With
                 Selection.Shadow = False
                 Selection.InvertIfNegative = False
                 Selection.Fill.OneColorGradient Style:=msoGradientVertical, Variant:=4, _
                 Degree:=3.13725490196078E-02
                 With Selection
                     .Fill.Visible = True
                     .Fill.ForeColor.SchemeColor = 18
                 End With
    
             Case "ORDER_LINE - E FF"
            ' do whatever

    End Select
    
    Next s
    
    End With
    
End Sub