Forum Discussion

ratishkp's avatar
ratishkp
Copper Contributor
Oct 01, 2019
Solved

Set Auto Refresh to Pivot Table as Source Data Changes

Overview:

1. Have a Master table where Cost per Month Per Item is added. 

2. Using this Master table, have created a Pivot tables where i create new views about the data like Cost per Category

 

Problem Statement

each time i change the Master Table, I need to manually right click and refresh the Pivot table so that the corresponding tables have accurate data

 

Question

How do i have a Refresh Button added that i can Anyone in the team to click if they change the Master table? or Auto Refresh Pivot table when Master table data is changed?

Macros and VBA
Pivot
  • Smitty Smith's avatar
    Smitty Smith
    Oct 01, 2019

    ratishkp You can assign this to a shape or button:

     

    Sub foo()

      ActiveWorkbook.RefreshAll

    End Sub

     

    Or if the PivotTable is on another sheet, you can add ActiveWorbook.RefreshAll to the Worksheet_Activate event, and it will automatically update whenever the sheet is selected.

2 Replies

Sort By
  • Smitty Smith's avatar
    Smitty Smith
    Former Employee
    Oct 01, 2019

    ratishkp You can assign this to a shape or button:

     

    Sub foo()

      ActiveWorkbook.RefreshAll

    End Sub

     

    Or if the PivotTable is on another sheet, you can add ActiveWorbook.RefreshAll to the Worksheet_Activate event, and it will automatically update whenever the sheet is selected.

Resources