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
Highlighted
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
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
Tabs and Dark Mode
cjc2112 in Discussions on
30 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
29 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies