none
Automatic Pivot Table Refesh VBA code. RRS feed

  • Question

  • Hi Guys,

    I am working on excel template auto refresh table excel. but i am unable to make it work. Do you have any VBa code to make the whole pivot table refresh by itself automatically when someone input new data in. Hope you can help me out.

    best Regards,

    Friday, February 9, 2018 11:32 PM

All replies

  • Hi Thainguyens2k,

    You had asked,"Do you have any VBa code to make the whole pivot table refresh by itself automatically when someone input new data in."

    Please try to refer example below.

    Private Sub Worksheet_Change(ByVal Target As Range)
        Worksheets(“SheetName”).PivotTables(“PivotTableName”).PivotCache.Refresh
    End Sub

    If you have multiple pivot tables in a sheet then you can refer code below to refresh them all.

    Private Sub Worksheet_Change(ByVal Target As Range)
    
    Dim pt As PivotTable
     
     For Each pt In Worksheets("PivotTable").PivotTables
    
    pt.PivotCache.Refresh
    
    Next
    
    
    End Sub

    Reference:

    PivotTable.RefreshTable Method (Excel)

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, February 12, 2018 2:24 AM
    Moderator