Duplicating the Bar and Average Chart

[MrsLisa]

Hello everyone. Chihiro and P45cal were a help getting me to this point in developing this charts. Unfortunately I hit another roadblock. I need to have up to 12 variations of this chart in the same workbook. All 12 will use the same brands but each will be filtered by a different attribute. When I copy the worksheet the code no longer works – it gives me the same “aver” on the copied worksheet. I tried to adjust the code and rename the pivot tables to no avail. Please see the sample worksheet. (My sample only has 8 attributes, but my originals has many more).

 

[Luke M]

Paging @Chihiro and @p45cal

 

[NARAYANK991]

Hi ,

The following code seems to work :

Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)
'           On Error Resume Next
             If Target.Name = "PivotTable2" Then  'tweak
                Application.EnableEvents = False
               Set pt = Target.CalculatedFields
               pt.Item("aver").StandardFormula = Evaluate("GETPIVOTDATA(""Average of Score""," & Target.TableRange1.Cells(1).Address(0, 0, , 1) & ")")  'tweak x 2
             End If
'           On Error GoTo 0
             Application.EnableEvents = True
End Sub

Narayan

 

[MrsLisa]

Hi ,

The following code seems to work :

Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)
'           On Error Resume Next
             If Target.Name = "PivotTable2" Then  'tweak
                Application.EnableEvents = False
               Set pt = Target.CalculatedFields
               pt.Item("aver").StandardFormula = Evaluate("GETPIVOTDATA(""Average of Score""," & Target.TableRange1.Cells(1).Address(0, 0, , 1) & ")")  'tweak x 2
             End If
'           On Error GoTo 0
             Application.EnableEvents = True
End Sub

Narayan

 

[MrsLisa]

Thank you. I'm sorry my attachment and my message wasn't not clear. Please see this version. The copied sheet is giving me an error message.

 

[p45cal]

I need to have up to 12 variations of this chart in the same workbook. All 12 will use the same brands but each will be filtered by a different attribute.

Shouldn't be too difficult; Will all 12 charts/pivots be looking at the same data (table)? If so there is no need to copy the data table when you copy the sheets, and it will make it simpler to tweak.

 

[MrsLisa]

Shouldn't be too difficult; Will all 12 charts/pivots be looking at the same data (table)? If so there is no need to copy the data table when you copy the sheets, and it will make it simpler to tweak.

Hi p45cal. I've been studying what you've showed me thus far. Yes all charts will run from the same data. As you will see in the worksheet I uploaded below, the does work but only as i click on each worksheet. Per your previous suggestion, I made sure that all pivots tables point to the same table as the data source.

I'm not sure the best approach to make sure all charts refresh after making changes to the slicer on the Main Menu tab so the charts are correct when we export to ppt. Thank you again for your help.

 

[p45cal]

Looking at your workbook I see the charts on separate sheets are (exactly?) the same except for the attribute they're filtered for. From the instructions on the Main sheet I see the aim is to produce a PowerPoint presentation with 13 charts, one per slide.
As long as the workbook itself is not for general distribution I'd like to propose a simpler solution which involves only one chart, one pivot cache. The code to produce the charts at the moment cycles through all the sheets, instead I suggest the code cycles through the attributes, copying the chart for each attribute to PowerPoint. That way you only have to spruce up one chart instead of 12+ and you'll get consistency of output. The Main sheet would be the same as now (although if you didn't want to chart all attributes you could also have a slicer to limit those too).
Feasible?

 

[MrsLisa]

Wow that would be fantastic! But will I be able to select various brands (between 5 - 12 brands)? Would that be a problem? I will be using to populate my data for me and my team and in no way will I distribute the workbook. I am learning so much. Thank you P45cal.

 

[p45cal]

see attached

 

[MrsLisa]

This is perfect! Now let me dissect the code. Thank you very much.