Macro - Refresh all Pivot tables

[dysfunction]

Is there a way I can have a macro that will refresh every single Pivot table in an excel workbook.

I can write one but it requires me to name every single pivot table which is a bit of a drag really. I would like something a bit simpler...

Thanks!

 

[Jonty]

Hi dysfunction

Apparently in Excel 2007 there is a 'Refresh All' command (with the shortcut Ctrl + Alt + F5). This should do the trick, but I don't know what version of Excel you're running?

Kind regards

 

[dysfunction]

I have excel 2003. The ctrl+alt+F5 doesn't do anything.

Is there a way of writing that command within a macro?

 

[Jonty]

Hi dysfunction

Hmm, I'm not honestly sure, I only have Excel 2007 to test on. I would imagine it is possible, so macros can be very powerful, but I will have a gander tomorrow at work (they use an earlier version of Excel).

Kind regards

Jonty

Edit ~ Having searched for this problem, there are people who have written this macro, so it must be possible. I'll try and find some code examples for you.

 

[dysfunction]

Ok thanks!

 

[dysfunction]

Found this and it works perfectly!

Sub AllWorkbookPivots()
Dim pt As PivotTable
Dim ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets
For Each pt In ws.PivotTables
pt.RefreshTable
Next pt
Next ws
End Sub

 

[Jonty]

Hi dysfunction

Snap! haha I was just testing it in Excel 2007, but I didn't have a pivot table to hand. Anyway, you saved me the job. Well done for finding the solution

Kind regards

Jonty

Edit ~ For those interested, there are many pivot table formulas on OZGrid.com.

 

[dysfunction]

Thanks for the help though!

 

[Jonty]

Thanks for the help though!

Hi dysfunction

No problem To be honest I only recently came across pivot tables at work, so I'm still curious about them. Anyway, it's interesting to know.

Kind regards