• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Duplicating the Bar and Average Chart

MrsLisa

New Member
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).
 

Attachments

  • chandoo30597Sample 2.xlsm
    164.8 KB · Views: 16
Hi ,

The following code seems to work :
Code:
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
 
Hi ,

The following code seems to work :
Code:
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
 
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.
 

Attachments

  • Sample test.xlsm
    45.3 KB · Views: 4
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.
 
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.
 

Attachments

  • Chart Generator 6.xlsm
    364.4 KB · Views: 3
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?
 
Last edited:
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.
 
Back
Top