Auto-refresh Pivot Tables when souce data changes

[glee0511]

Hi there,

I'd like to know how to have a pivot table automatically refresh itself as soon as data is changed in the source table. My source data and pivot table are on the same workbook, and I have 2 pivot tables(in different sheets) with same source data (in another sheet).

Thanks in advance..

 

[Oeldere]

Data => refresh

 

[glee0511]

Thank you, but what I need is the auto-refresh. I have seen some steps on the other forum but it seems details are incomplete.

 

[circledchicken]

Hi,

Since your PivotTable's are stored in separate worksheets from the source data, another (less expensive) option might be to refresh the PivotTables when you activate any of the PivotTable worksheets. One way to do this might be as follows:

In a regular module enter the following:

Sub ptRefresh()


    Dim pt As PivotTable
    
    For Each pt In ActiveSheet.PivotTables
        pt.RefreshTable
    Next pt


End Sub

Assume Sheet2 and Sheet3 contain the PivotTables. In each of the associated Worksheet modules enter:

Private Sub Worksheet_Activate()
    Call ptRefresh
End Sub

If you really want them to refresh as soon as the data changes (although I don't think it's a good idea), you can apply the same procedure, but using the Worksheet_Change event in the Sheet1 (source data) worksheet module, something like this:

Private Sub Worksheet_Change(ByVal Target As Range)
    Call ptRefresh
End Sub

If you do actually use this, you should add further restrictions as required depending on the setup of your source data so only changes in that particular region of the worksheet activate the refresh procedure.

For more on the distinction between regular and worksheet modules see:
Excel VBA -- Adding Code to Excel Workbook

For other PivotTable refresh options see:
Refresh Pivot Table via VBA. Update Pivot Table With VBA
MS Excel: Automatically refresh pivot table when data in a sheet changes in Excel 2003/XP/2000/97