I am working on a dashboard that has 4 Cascading Combo Boxes, where one filters the next which filters the next. They are connected to a linked cell with a named range.
On another worksheet I have Pivot Table that is connected to a Pivot Chart on the same page as my Combo Boxes.
Long story short, as one combo box changes a filter on my pivot table changes, which changes the pivot chart.
I have done this two ways, one way performs half the time but they both end up the same way. I can change the filters but the pivot table will not show values.
(table with filters applied but no values showing)
I have pieced this together and mended it to fit my needs.
If there is anything you see that can improve the code such as a better way to do something or speed it up feel free to let me know!
Any ideas?
On another worksheet I have Pivot Table that is connected to a Pivot Chart on the same page as my Combo Boxes.
Long story short, as one combo box changes a filter on my pivot table changes, which changes the pivot chart.
I have done this two ways, one way performs half the time but they both end up the same way. I can change the filters but the pivot table will not show values.
(table with filters applied but no values showing)
I have pieced this together and mended it to fit my needs.
Code:
Option Explicit
Sub changeFilters()
Dim pt As PivotTable
Dim pf As PivotField
Dim pi As PivotItem
Dim wsChart As Worksheet
Dim wsPivot As Worksheet
Dim selCat As Variant
Dim selSub As Variant
Dim selLoc As Variant
Dim selCust As Variant
Set wsChart = ThisWorkbook.Sheets("CHART")
Set wsPivot = ThisWorkbook.Sheets("Pivot")
Set pt = ThisWorkbook.Sheets("Pivot").PivotTables("PT1")
Set selCat = ThisWorkbook.Sheets("CHART").Range("selCat")
Set selSub = ThisWorkbook.Sheets("CHART").Range("selSub")
Set selLoc = ThisWorkbook.Sheets("CHART").Range("selLoc")
Set selCust = ThisWorkbook.Sheets("CHART").Range("selCust")
pt.ManualUpdate = True
Application.ScreenUpdating = False
For Each pi In pt.PivotFields("CATEGORY").PivotItems
Select Case pi.Name
Case [selCat]
pi.Visible = True
Case Else
pi.Visible = False
End Select
Next pi
'Removes pivot items from pivot table except those cases defined below (by looping through)
For Each pi In pt.PivotFields("SUB-CATEGORY").PivotItems
Select Case pi.Name
Case [selSub]
pi.Visible = True
Case Else
pi.Visible = False
End Select
Next pi
For Each pi In pt.PivotFields("LOCATION").PivotItems
Select Case pi.Name
Case [selLoc]
pi.Visible = True
Case Else
pi.Visible = False
End Select
Next pi
For Each pi In pt.PivotFields("CUSTOMER").PivotItems
Select Case pi.Name
Case [selCust]
pi.Visible = True
Case Else
pi.Visible = False
End Select
Next pi
'turn on automatic update / calculation in the Pivot Table
pt.ManualUpdate = False
pt.PivotCache.Refresh
Application.ScreenUpdating = True
End SubIf there is anything you see that can improve the code such as a better way to do something or speed it up feel free to let me know!
Any ideas?
