VBA to refresh all pivot tables [Solved/Closed]

Fwong 12 Posts Friday May 15, 2015Registration date June 5, 2015 Last seen - Jun 5, 2015 at 03:22 AM - Latest reply: Fwong 12 Posts Friday May 15, 2015Registration date June 5, 2015 Last seen
- Jun 5, 2015 at 04:17 AM
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

See more 

1 reply

Fwong 12 Posts Friday May 15, 2015Registration date June 5, 2015 Last seen - Jun 5, 2015 at 04:17 AM
0
Thank you
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,