How to refresh pivot table when source data update in Excel?

    Pivot table is one the most useful visualization is excel reporting projects. Its greatest strength is its ability to structure, summarize and display large amounts of data. Pivot tables can also be used to determine whether there is a relation between the row variable and the column variable or not. 

     Pivot table can display grand totals for columns, rows, or for the whole measure. It can also display subtotals for columns. Also, anyone can create a calculated field based on input data source. Using pivot table anyone can validate their input data in short time. which is very important feature in terms of data quality check.

    Refreshing pivot is a regular task when it come to reporting generation pivot. Thus, it need to be automate pivot update process before producing the report. This can be achieved by adding a small VBA code which will refresh all pivot in the workbook.

Here is the code which you can utilize in your excel project to refresh all pivot tables in workbook:

Sub Refresh_All_Pivot()

' Author: Dreams24

' Written for VBA Tricks and tips blog

' http://vbatricksntips.com

'Declare variables

Dim wSht As Worksheet

Dim tPvt As PivotTable

'Loop through all sheet

For Each wSht In Application.ActiveWorkbook.Worksheets

    'Loop through all pivot table in a sheet

    For Each tPvt In wSht.PivotTables

    

       'Refresh and updated pivot table report from data source

        tPvt.RefreshTable

        

        'Refresh pivot table cache

        tPvt.PivotCache.Refresh

    Next tPvt

    

Next wSht

MsgBox "All Pivot tables refreshed successfully!!!", vbInformation

End Sub