Forum Discussion

learnerjane's avatar
learnerjane
Copper Contributor
Aug 01, 2022
Solved

Pivot Table Refresh Not Working

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 t...
excel
  • HansVogelaar's avatar
    HansVogelaar
    Aug 01, 2022

    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

HansVogelaar's avatar
HansVogelaar
MVP
Aug 01, 2022

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.

learnerjane's avatar
learnerjane
Copper Contributor
Aug 01, 2022

HansVogelaar 

 

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

  

  • HansVogelaar's avatar
    HansVogelaar
    MVP
    Aug 01, 2022

    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

    • learnerjane's avatar
      learnerjane
      Copper Contributor
      Aug 01, 2022
      My bad. I totally missed it. Thanks Hans.

Resources