VBA to refresh all pivot tables [Solved/Closed]

Report
Posts
12
Registration date
Friday May 15, 2015
Status
Member
Last seen
June 5, 2015
-
Posts
12
Registration date
Friday May 15, 2015
Status
Member
Last seen
June 5, 2015
-
Hello,

One of my worksheets has about 40 pivot tables. I know that there is a pivot table option to "refresh tables when opening file". But my raw data can be amended any minute and I do not want to close and open the file to refresh all of the pivot tables (is that what the pivot table option means?).

I just wondered if I could have a macro to refresh all of the pivot tables in one go whenever I want to?

Thanks and await your help,
Felicia

1 reply

Posts
12
Registration date
Friday May 15, 2015
Status
Member
Last seen
June 5, 2015

Hi,

I think I got the macro to do this:
Sub RefreshAll()
Dim pvt As PivotTable
dim sh as Worksheet

for each sh in Worksheets
For Each pvt In sh.PivotTables
pvt.RefreshTable
Next pvt
next sh

End Sub

Thanks,

Subscribe To Our Newsletter!

The Best of CCM in Your Inbox

Subscribe To Our Newsletter!