Log In
Register
Home
New Member
March 13, 2018
Offline
I am trying to create a dashboard where the option the user picks in a combo box is filtered in all the pivot tables on another sheet. The code seems to be working fine except when the filtered named doesn't exist in a specific pivot table. What is happening is its forcing the Filter to be what the selection is whether it exists or not and then uses that data in the pivot table that was there from the previous filter as the data for the new non-existent filter. When I realized this after going 1 by 1 through each selection, I tried to manually recalculate the pivot table and it wouldn't refresh as 0. Then I went into the data table added that selection with 0s in the data fields but it still pulls the old filtered data almost like its force saved in the background.
Any one have any idea on how to fix this? The code I have used is below. I should preface this with the fact that I am no coder at all; when ever I need code I look through forums and try to modify code I find until I get it to work through trial and error.
Private Sub ComboBox1_Change()
Dim sheet As Worksheet
Dim pt As PivotTable
Dim ptField As PivotField
Set sheet = ThisWorkbook.Worksheets("PivotTables")
For Each pt In sheet.PivotTables
Set ptField = Nothing
On Error Resume Next
Set ptField = pt.PivotFields("Provider Name")
ptField.CurrentPage = Me.ComboBox1.Value
Next pt
End Sub
VIP
Trusted Members
June 25, 2016
Offline
Hi Kyle
Welcome to the forum.
I would suggest you use a slicer instead of a combo box when dealing with Pivot Tables. You don't need codes at all and it is easy to implement.
There are many articles here at MOTH on this subject. Here is one of them https://www.myonlinetraininghu.....licer-that
Sunny
VIP
Trusted Members
June 25, 2016
Offline
If you need codes, then maybe you can give this a try :
Private Sub ComboBox1_Change()
Dim ws As Worksheet
Dim wb As Workbook
Dim pt As PivotTable
Dim pf As PivotField
Set wb = ActiveWorkbook
For Each ws In wb.Worksheets
For Each pt In ws.PivotTables
Set pf = Nothing
On Error Resume Next
Set pf = pt.PivotFields("PROVIDER NAME")
'Clear Out Any Previous Filtering
pf.ClearAllFilters
'Set the filter
pf.CurrentPage = ComboBox1.Value
Next pt
Next ws
End Sub
Please note that the filter is set to ALL if the selected name is not found in the PT.
Good luck
Sunny
1 Guest(s)
