Macro to update all pivots in a workbook

[df9864]

Hopefully this is a relatively simple - but does anyone have some code which will cycle through all the worksheets of a workbook and refresh any pivot tables.
Not all worksheets will have a pivot and there could be more than 1 pivot table per worksheet (tho this is unlikely and not a deal breaker).

Any help would be much appreciated.
Thanks

 

[Comfy]

Try this:

Sub Pvt_Refresh()
Dim wb As Workbook, ws As Worksheet
Dim pvt As PivotTable

Set wb = ActiveWorkbook

For Each ws In wb.Worksheets
    For Each pvt In ws.PivotTables
        ws.PivotTables(pvt).PivotCache.Refresh
    Next
Next
End Sub

I haven't had a chance to validate it.

 

[df9864]

Thanks for the code - looked good to me but I got the following error as it tried to refresh the first pivot (which it found by name)

Run-time error '1004':
Method 'PivotTables' of object_'Worksheet' failed.

 

[Comfy]

Sub Pvt_Refresh()
Dim wb As Workbook, ws As Worksheet
Dim pvt As PivotTable

Set wb = ActiveWorkbook

For Each ws In wb.Worksheets
    For Each pvt In ws.PivotTables
        ws.PivotTables(pvt.Name).PivotCache.Refresh
    Next
Next
End Sub

Missed off .Name

Try it again

 

[df9864]

That's the ticket - thanks alot !!