Pivot Chart Update Macro

[Zeptune]

Hello,

I have built a pivot chart that people can change with slicers.

My issue is when people make new selections in the slicers the pivot chart loses it's formatting.

Selections (products) can vary. There will always be at least two, but can be up to 5 or 6 (if not more).

My pivot chart has two axis, 1) Packages 2) Price. The Package bar color should match the price line color for that same selection (product).

My question, Could someone point me in the direction of a macro that I can use to properly format the chart after a user makes his/her selections?

Below is a copy of my chart...

Any help you could provide me would be much appreciated.

 

[Zeptune]

I have come up with the below code myself.

My issue is that the series can be different types of data. However, the chart type for the values will always be the same.

For example, value 'Pkgs' will always need to be a Stacked Column and value 'Avg Pkg Prc' will always need to be a line.

In the code below series 1, 2 & 3 are Stacked Column and series 4,5, & 6 are Line.

Based on my example, It's possible that series 1 & 2 will need to be Stacked Column with Series 3 & 4 will be Line... along with many other combinations based on the pivot 'value' field.

Any help in adjusting my code to make this work would be appreciated.

Thanks... Zep

Sub CreatePivotChart()

Application.ScreenUpdating = False

Dim myPT As PivotTable
Set myPT = ThisWorkbook.Sheets("Pivot Shifting").PivotTables(1)
myPT.PivotSelect ("")

Charts.Add

ActiveChart.Location Where:=xlLocationAsNewSheet

  ActiveChart.FullSeriesCollection(1).ChartType = xlColumnStacked
  ActiveChart.FullSeriesCollection(1).AxisGroup = 1
  ActiveChart.FullSeriesCollection(1).Select
  With Selection.Format.Fill
  .ForeColor.ObjectThemeColor = msoThemeColorAccent1
  .Transparency = 0.5
  .Solid
  End With
  Selection.Format.ThreeD.BevelTopInset = 5
  Selection.Format.ThreeD.BevelTopDepth = 2
  
  ActiveChart.FullSeriesCollection(2).ChartType = xlColumnStacked
  ActiveChart.FullSeriesCollection(2).AxisGroup = 1
  ActiveChart.FullSeriesCollection(2).Select
  With Selection.Format.Fill
  .ForeColor.ObjectThemeColor = msoThemeColorAccent2
  .Transparency = 0.5
  .Solid
  End With
  Selection.Format.ThreeD.BevelTopInset = 5
  Selection.Format.ThreeD.BevelTopDepth = 2
  
  ActiveChart.FullSeriesCollection(3).ChartType = xlColumnStacked
  ActiveChart.FullSeriesCollection(3).AxisGroup = 1
  ActiveChart.FullSeriesCollection(3).Select
  With Selection.Format.Fill
  .ForeColor.ObjectThemeColor = msoThemeColorAccent3
  .Transparency = 0.5
  .Solid
  End With
  Selection.Format.ThreeD.BevelTopInset = 5
  Selection.Format.ThreeD.BevelTopDepth = 2
  
  ActiveChart.FullSeriesCollection(4).ChartType = xlLine
  ActiveChart.FullSeriesCollection(4).AxisGroup = 2
  ActiveChart.FullSeriesCollection(4).Select
  With Selection.Format.Line
  .ForeColor.ObjectThemeColor = msoThemeColorAccent1
  End With
  
  ActiveChart.FullSeriesCollection(5).ChartType = xlLine
  ActiveChart.FullSeriesCollection(5).AxisGroup = 2
  ActiveChart.FullSeriesCollection(5).Select
  With Selection.Format.Line
  .ForeColor.ObjectThemeColor = msoThemeColorAccent2
  End With
  
  ActiveChart.FullSeriesCollection(6).ChartType = xlLine
  ActiveChart.FullSeriesCollection(6).AxisGroup = 2
  ActiveChart.FullSeriesCollection(6).Select
  With Selection.Format.Line
  .ForeColor.ObjectThemeColor = msoThemeColorAccent3
  End With
  
  ActiveChart.ShowAllFieldButtons = False
  ActiveChart.SetElement (msoElementChartTitleAboveChart)
  ActiveChart.ChartTitle.Text = "Weekly PPG Price Shifting"
  ActiveChart.SetElement (msoElementLegendBottom)

Application.ScreenUpdating = True

End Sub

 

[NARAYANK991]

Hi ,

Have you checked out this :

http://peltiertech.com/pivot-chart-formatting-changes/

Narayan

 

[Zeptune]

@NARAYANK991 Thanks for your reply. Yes, I have checked out that site. My issue is that I have hundreds for options for ways to slice the data. Therefore I need a more dynamic option.

Cons of the link you sent...
If you have a lot of slices then it becomes very tedious work to go through each slice and reapply the customized formatting. And it will also require maintenance in future periods.

I do appreciate the reply!

Thanks!

 

[Zeptune]

Below is the data that I am working with... the row with the 1, 2 & 3 can expand and contract for both 'Pkgs' and 'Avg Pkg Prc' (will always have the same number of results for both 'Pkgs and 'Avg Pkg Prc').

The info here is the closest (that I have found) to fitting my needs. (ie. Updated Pivot Chart based on the new number of series)

However my VBA skills are not good enough to make it work.

 

[NARAYANK991]

Hi ,

If you want help on VBA , please upload your workbook with the data , the pivot table and the pivot chart in it.

Narayan

 

[Zeptune]

Below is a link to my file.

My file includes...

1) The user selection page (User will end up selecting 2 or more PPG's)

2) The chart that the user should see after making their selections. (Chart needs to be dynamic based on the users selections, with 'Pkgs' always being a stacked bar, 50% transparent and 'Avg Pkg Prc' always being a line on the 2nd axis matching it's stacked bar counter part)

3) The pivot table (Rows will stay constant. The columns will change depending on the number of PPG's selected.)

4) Dummy Data

5) Two macros that are failing. 1) Creates a new chart based on the user selections (only with 3 PPG's selected) 2) Updates the current chart

I only need the macro that would 'update the chart' based on the users selections to work.

If the create chart macro worked, that would just be a bonus for me (when I first create the report).

Therefore, 'Update Current Chart' is #1 priority.

https://dl.dropboxusercontent.com/u/18830327/PSRTest.xlsm

Thanks!