Excel 2013 VBA Pivot Table Filters will change but values wont show

Question

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 combobox 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.

I have pieced this together and mended it to fit my needs.

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


 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 Sub

ecoffman ecoffman · Accepted Answer · 2015-08-26T13:55:03

I believe I figured it out!, on to the next issue. The solution was to add

ThisWorkbook.Sheets("Pivot").PivotTables("PT1").ClearAllFilters before the comboboxes.

Amended code below.

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")


Application.ScreenUpdating = False
pt.ManualUpdate = True

ThisWorkbook.Sheets("Pivot").PivotTables("PT1").ClearAllFilters



 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
 Application.ScreenUpdating = True




End Sub

Excel 2013 VBA Pivot Table Filters will change but values wont show

1 Answers