Pivot table refresh - VBA Code

**Raviprasad.k** · 12-14-2012, 06:32 AM

Dear friends,
I have a pivot table and what i need is everytime when the new data is added, the pivot table to should get refreshed with all the filter selected (including blanks) in every filter.
Currently the issue is, whenever i add a new data (fruits / dealer / stage / Total quantity) the item in the pivot filter which is comes after Blanks doesnt get selected.

Attached the spreadsheet for your help.

Thanks in advance

**VKS** · 12-14-2012, 06:54 AM

Hello Ravi, I am not too sure if this will work for you but still try the following
Go to your datasheet highlight the last row that has data and insert a new row above the last row of Pivot Table Data. Enter the values in this new row go back to your Pivot table and refresh i think you will get to see what you want to see.
Let me know if thats what you wanted.
Thanks/VKS

**Raviprasad.k** · 12-14-2012, 06:57 AM

Hello VKS,
Thanks for your suggestion but the data sheet is again automated to pick the data from different sheets, so it keeps adding below the last line in the summary. :-)
Doesnt work for me.

**6StringJazzer** · 12-14-2012, 08:19 AM

Your title asked for a VBA solution so here is VBA that will refresh and then include all values in the filters. This is specific to the file you posted.

Private Sub RefreshPivot()

   Sheet5.PivotTables("PivotTable8").PivotCache.Refresh

   ShowAllValues Sheet5.PivotTables("PivotTable8").PivotFields("Fruits")
   ShowAllValues Sheet5.PivotTables("PivotTable8").PivotFields("Dealer")
   ShowAllValues Sheet5.PivotTables("PivotTable8").PivotFields("Stage")

End Sub

Private Sub ShowAllValues(pf As PivotField)

   Dim i As Long

   For i = 1 To pf.PivotItems.Count
       pf.PivotItems(i).Visible = True
   Next i

End Sub