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.