Aug 01 2022 05:21 AM
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
Aug 01 2022 06:48 AM
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.
Aug 01 2022 07:06 AM
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
Aug 01 2022 07:20 AM - edited Aug 01 2022 07:27 AM
SolutionThanks, 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
Aug 01 2022 07:20 AM - edited Aug 01 2022 07:27 AM
SolutionThanks, 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