excel not refreshing all pivot tables

2
votes

I am trying to refresh pivot tables using vba.
I have code that modifies the underlying table query, and refreshes the query.
Once that is done, it refreshes the pivot tables, which in turn will refresh a summary sheet. For some reason, the final pivot table is not being refreshed. It will refresh normally.

Methods tried:

1.

Dim PC As PivotCache
For Each PC In ThisWorkbook.PivotCaches
    While DateDiff("s", PC.RefreshDate, Now) > 60
    PC.Refresh
    Wend
Next

2.

Sheets("Pivots").PivotTables("Pivot1").PivotCache.Refresh
Sheets("Pivots").PivotTables("Pivot2").PivotCache.Refresh
Sheets("Pivots").PivotTables("Pivot3").PivotCache.Refresh
Sheets("Pivots").PivotTables("Pivot4").PivotCache.Refresh

3.

ThisWorkbook.RefreshAll

I have tried including a DoEvents before refreshing pivot tables, after the refresh of the tables has occurred.
I have also tried copying the file to another file, and recreating it there. the effect stays, it simply moves to whichever pivot table it considers the last one created. File size is 279Kb as xlsb, and 336 Kb as xlsm.
Data for the tables is coming from SQL server 2012, and all tables are on the same excel sheet. The tables are updated, but the pivot is not.

1
vbaexcelexcel-2013
just a thought ... what if you put a hard Application.Wait statement after the data refresh? – Scott Holtzman
no luck with application.wait. adding a MsgBox and forcing a wait that way doesn't help either. – SeanC
Try by (re)setting data source range of the pivot in code before refresh. – PankajR
@PankajR, Sheets("Pivots").PivotTables("Pivot1").PivotCache.SourceData = "Table1" (etc) - no change – SeanC
Could it be that the data didn't change for that particular table and so it refreshed but you don't notice a difference? – Matt Cremeens

1 Answers

0
votes

Try something like

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