none
Auto update pivot table as data is updated

    Question

  • In Excel 2010 how can I auto update a pivot table as I enter new data?  Right now, I have to manually refresh the data in the pivot table which also updates the graph.

    Thanks,

    Mohsin Malik


    Mohsin M.

    Monday, February 20, 2012 6:56 PM

Answers

  • You could use the Worksheet_Change event of the sheet containing the source data. However, this disables Excel's undo feature, so it may be undesirable.

    If the pivottable is on a different sheet than the source data, you could use the Worksheet_Activate event of the sheet containing the pivottable:

    Right-click the sheet tab of the sheet with the pivottable.

    Select View Code from the context menu.

    Copy the following code into the module window:

    Private Sub Worksheet_Activate()
        Me.PivotTables(1).RefreshTable
    End Sub

    This assumes that the pivottable is the first one on the sheet (if there is only one, that'll always be true).

    As soon as the user activates the sheet, the pivottable and hence the pivotchart will be updated.


    Regards, Hans Vogelaar

    Monday, February 20, 2012 8:32 PM

All replies

  • You could use the Worksheet_Change event of the sheet containing the source data. However, this disables Excel's undo feature, so it may be undesirable.

    If the pivottable is on a different sheet than the source data, you could use the Worksheet_Activate event of the sheet containing the pivottable:

    Right-click the sheet tab of the sheet with the pivottable.

    Select View Code from the context menu.

    Copy the following code into the module window:

    Private Sub Worksheet_Activate()
        Me.PivotTables(1).RefreshTable
    End Sub

    This assumes that the pivottable is the first one on the sheet (if there is only one, that'll always be true).

    As soon as the user activates the sheet, the pivottable and hence the pivotchart will be updated.


    Regards, Hans Vogelaar

    Monday, February 20, 2012 8:32 PM
  • Auto updates now, Thanks!

     Mohsin


    Mohsin M.

    Tuesday, February 28, 2012 6:50 PM