SOLVED

Pivot Table Refresh Not Working

Occasional Contributor

I have a Pivot Table which is not getting refreshed when there is a change in no. of rows in the data source. I have a table as a data source, so whenever the rows got increased or decreased in the table, the pivot data source is getting updated without a problem. But the pivot is not refreshing when I click the Refresh or Refresh All button. Appreciate if someone can shed some light on this issue. @Hans Vogelaar

4 Replies

@learnerjane 

Could you attach a sample workbook (without sensitive data), or if that is not possible, make it available through OneDrive, Google Drive, Dropbox or similar? Thanks in advance.

@Hans Vogelaar 

 

I have shared the OneDrive link over DM.

For now I have fixed it with a VBA code. 

Below Subs didn't work, not sure why, and the manual refresh also:

Sub RefreshOneTable
ActiveSheet.PivotTables("PivotTable1").RefreshTable
End Sub
Sub RefreshActiveSheetPivotsOnly()
Dim tblPivot As PivotTable
For Each tblPivot In ActiveSheet.PivotTables
tblPivot.RefreshTable
Next tblPivot
End Sub


Working Code: 

Private Sub Worksheet_Change(ByVal Target As Range)
ThisWorkbook.RefreshAll
End Sub

  

best response confirmed by learnerjane (Occasional Contributor)
Solution

@learnerjane 

Thanks, I received your PM.

Your pivot table is filtered to display only data for HDL File Name = Contact.dat.

I disabled the Worksheet_Change event procedure, then deleted one of the Contact.dat rows.

Refreshing the pivot table from the PivotTable Analyze tab of the ribbon worked as expected.

I also added a new row to the source table with HDL File Name = Contact.dat. Again, refreshing the pivot table from the ribbon worked correctly.

Is it possible that you added/deleted rows for other HDL File Names? The pivot table wouldn't change as long as it is filtered on Contact.dat

My bad. I totally missed it. Thanks Hans.