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 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. HansVogelaar

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

4 Replies

Sort By
  • 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

Resources