SOLVED
Home

Set Auto Refresh to Pivot Table as Source Data Changes

%3CLINGO-SUB%20id%3D%22lingo-sub-887015%22%20slang%3D%22en-US%22%3ESet%20Auto%20Refresh%20to%20Pivot%20Table%20as%20Source%20Data%20Changes%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-887015%22%20slang%3D%22en-US%22%3E%3CP%3EOverview%3A%3C%2FP%3E%3CP%3E1.%20Have%20a%20Master%20table%20where%20Cost%20per%20Month%20Per%20Item%20is%20added.%26nbsp%3B%3C%2FP%3E%3CP%3E2.%20Using%20this%20Master%20table%2C%20have%20created%20a%20Pivot%20tables%20where%20i%20create%20new%20views%20about%20the%20data%20like%20Cost%20per%20Category%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EProblem%20Statement%3C%2FP%3E%3CP%3Eeach%20time%20i%20change%20the%20Master%20Table%2C%20I%20need%20to%20manually%20right%20click%20and%20refresh%20the%20Pivot%20table%20so%20that%20the%20corresponding%20tables%20have%20accurate%20data%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EQuestion%3C%2FP%3E%3CP%3EHow%20do%20i%20have%20a%20Refresh%20Button%20added%20that%20i%20can%20Anyone%20in%20the%20team%20to%20click%20if%20they%20change%20the%20Master%20table%3F%20or%20Auto%20Refresh%20Pivot%20table%20when%20Master%20table%20data%20is%20changed%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-887015%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EPivot%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-887112%22%20slang%3D%22en-US%22%3ERe%3A%20Set%20Auto%20Refresh%20to%20Pivot%20Table%20as%20Source%20Data%20Changes%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-887112%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F418244%22%20target%3D%22_blank%22%3E%40ratishkp%3C%2FA%3E%26nbsp%3BYou%20can%20assign%20this%20to%20a%20shape%20or%20button%3A%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ESub%20foo()%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%20ActiveWorkbook.RefreshAll%3C%2FP%3E%0A%3CP%3EEnd%20Sub%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EOr%20if%20the%20PivotTable%20is%20on%20another%20sheet%2C%20you%20can%20add%20ActiveWorbook.RefreshAll%20to%20the%20Worksheet_Activate%20event%2C%20and%20it%20will%20automatically%20update%20whenever%20the%20sheet%20is%20selected.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-887963%22%20slang%3D%22en-US%22%3ERe%3A%20Set%20Auto%20Refresh%20to%20Pivot%20Table%20as%20Source%20Data%20Changes%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-887963%22%20slang%3D%22en-US%22%3E%3CP%3EThank%20you%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F10827%22%20target%3D%22_blank%22%3E%40Smitty%20Smith%3C%2FA%3E%26nbsp%3B!%20that%20worked!%3C%2FP%3E%3C%2FLINGO-BODY%3E
ratishkp
New Contributor

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?

2 Replies
Solution

@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.

Thank you @Smitty Smith ! that worked!

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
46 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
Extentions Synchronization
Deleted in Discussions on
3 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
29 Replies