Refresh Pivot Tables 1

[robcarr]

Dear All,

I have a workbook with 20 different sheets with about 15 pivot tables, is there away to refresh all pivot tables in 1 go.the tables are on different sheets with different names, I know how to do the coding individually, but I was wondering if it was possible to just refresh regardless of the pivot table name, thus reducing coding and time.

any help greatly appreciated.

 

[dsi]

Try this:

Sub RefreshAllPivotTables()
    Dim ws As Worksheet
    Dim pt As PivotTable
    For Each ws In ThisWorkbook.Worksheets
        For Each pt In ws.PivotTables
            pt.RefreshTable
        Next
    Next
    Set pt = Nothing
    Set ws = Nothing
End Sub

 

[robcarr]

thanks for that worked a treat.

Rob.

 

[comconrk]

Thanks for this post on Pivot Tables, as it really helped to clean up my code.

I do have one problem. I have 5 pivot tables in this worksheet and all of them run through this new code as it appears on your post.
However, a couple of them do not refresh properly. After the code runs, I click on the pivot table, then the refresh button and the rows change to the correct value.

How can I make this automatic?

Thanks very much