Previous Topic Next Topic

VBA to refresh all pivot tables

Solved/Closed
Fwong Posts 11 Registration date Friday May 15, 2015 Status Member Last seen June 5, 2015 - Jun 5, 2015 at 03:22 AM
Fwong Posts 11 Registration date Friday May 15, 2015 Status Member Last seen June 5, 2015 - 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

Related:

1 response

Answer 1 / 1
Fwong Posts 11 Registration date Friday May 15, 2015 Status Member Last seen June 5, 2015
Jun 5, 2015 at 04:17 AM
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,
0