[FONT=Open Sans, Helvetica Neue, Helvetica, Arial, sans-serif]I am trying to create a dashboard where the user picks an item in a combo box and that is filtered to all the pivot tables on another sheet. The code seems to be working fine except when the filtered item doesn't exist in a specific pivot table. [/FONT]
[FONT=Open Sans, Helvetica Neue, Helvetica, Arial, sans-serif]What is happening is, it is forcing that filtered item into the pivot table and is using the previously filtered data as its data. When I try to manually recalculate it nothing happens. I then went to the source table and added the filtered item and filled it with 0's for the data fields in hopes that it would just pull the 0s; however, it seem to be stuck on the previously filtered forced data set. When I try to dig into the data (right click show details) it brings up the data from the forced data set. It almost like its forced saved in the background and I cant seem to overwrite it. In the pivot table options, under the data tab I have "Retain Items Deleted from Data Source" selected to None but I have tried all 3 options and none work.[/FONT]
[FONT=Open Sans, Helvetica Neue, Helvetica, Arial, sans-serif]Below is the code I used. I should warn, I am not a coder by stretch of the imagination. I am just relatively savy in excel and get chosen for projects that I sometimes need to use code for. Typically I search the internet and find code from forums and copy paste. And then I modify through trial and error until I get it to work. [/FONT]
[FONT="]Private Sub ComboBox1_Change()[/FONT]
[FONT="]Dim sheet As Worksheet
Dim pt As PivotTable
Dim ptField As PivotField[/FONT]
[FONT="]Set sheet = ThisWorkbook.Worksheets("PivotTables")[/FONT]
[FONT="]For Each pt In sheet.PivotTables[/FONT]
[FONT="]Set ptField = Nothing[/FONT]
[FONT="]On Error Resume Next[/FONT]
[FONT="]Set ptField = pt.PivotFields("Provider Name")[/FONT]
[FONT="]ptField.CurrentPage = Me.ComboBox1.Value[/FONT]
[FONT="]Next pt[/FONT]
[FONT="]End Sub[/FONT]
[FONT=Open Sans, Helvetica Neue, Helvetica, Arial, sans-serif]What is happening is, it is forcing that filtered item into the pivot table and is using the previously filtered data as its data. When I try to manually recalculate it nothing happens. I then went to the source table and added the filtered item and filled it with 0's for the data fields in hopes that it would just pull the 0s; however, it seem to be stuck on the previously filtered forced data set. When I try to dig into the data (right click show details) it brings up the data from the forced data set. It almost like its forced saved in the background and I cant seem to overwrite it. In the pivot table options, under the data tab I have "Retain Items Deleted from Data Source" selected to None but I have tried all 3 options and none work.[/FONT]
[FONT=Open Sans, Helvetica Neue, Helvetica, Arial, sans-serif]Below is the code I used. I should warn, I am not a coder by stretch of the imagination. I am just relatively savy in excel and get chosen for projects that I sometimes need to use code for. Typically I search the internet and find code from forums and copy paste. And then I modify through trial and error until I get it to work. [/FONT]
[FONT="]Private Sub ComboBox1_Change()[/FONT]
[FONT="]Dim sheet As Worksheet
Dim pt As PivotTable
Dim ptField As PivotField[/FONT]
[FONT="]Set sheet = ThisWorkbook.Worksheets("PivotTables")[/FONT]
[FONT="]For Each pt In sheet.PivotTables[/FONT]
[FONT="]Set ptField = Nothing[/FONT]
[FONT="]On Error Resume Next[/FONT]
[FONT="]Set ptField = pt.PivotFields("Provider Name")[/FONT]
[FONT="]ptField.CurrentPage = Me.ComboBox1.Value[/FONT]
[FONT="]Next pt[/FONT]
[FONT="]End Sub[/FONT]