Refreshing Pivot Tables

[JeffGrant]

Hi All,

I have a sub like this.

Sub RefreshPivotTables()
    Sheet1.Activate
    Call RefreshActiveSheetPivotTables
    Sheet2.Activate
    Call RefreshActiveSheetPivotTables
    Sheet3.Activate
    Call RefreshActiveSheetPivotTables
End Sub

Which calls

Sub RefreshActiveSheetPivotTables()
   Dim PivotTbl As PivotTable
   On Error Resume Next

   For Each PivotTbl In ActiveSheet.PivotTables
      PivotTbl.RefreshTable
   Next PivotTbl
End Sub

On a semi-regular basis, not all of the pivot tables are freshed on the active sheet. I can refresh them manually without any issues, but they are just missed in the loop.
It is not any one particular pivot table that is missed, the missed pivot table changes.
What am I doing incorrectly?
Any guidance is greatly appreciated please.

 

[MCLIFTO8]

Jeff can you try replacing ".RefreshTable" with ".PivotCache.Refresh"?

 

[JeffGrant]

Thanks MCLIFTO8. Much appreciated.

However, I know understand why this issue occured. I cam across this article:

Run-Time Error 1004: MS Excel is refreshing some data. Please try again later using Query

I have a dynamic query in a sheet that uses a parameter in cell C1. So, In column A there are codes, and in column B are those code descriptions and based on the value in cell C1, the query will re...

stackoverflow.com

which game me the idea that the querry is the same table as some VBA code at the same time. Which then threw up the 1004 error. So as long as I allow the querry to finish before the vba code kicks in, then all is fine.

Makes sense now

 

[JeffGrant]

I actually found an oldy but a goody solution. Sometimes we can forget that the simplest things in life solve the issue.

The issue was that I had querries and vba accessing tables at the same time, when really what was needed was to have the queries finish first, thhe run the vba code.

Sub RefreshPivotTables()
    Do While Application.CalculationState <> xlDone
       Refresh all the queries
Loop

so by the tme the VBA get sthis code, all of the queries have been refreshed and no more clashes

    Sheet1.Activate
    Call RefreshActiveSheetPivotTables
    Sheet2.Activate
    Call RefreshActiveSheetPivotTables
    Sheet3.Activate
    Call RefreshActiveSheetPivotTables
End Sub

This solution also fixed another post I had using the With statement.

 

[RoryA]

Just FYI, you could add an optional worksheet argument to the refresh routine:

Sub RefreshActiveSheetPivotTables(Optional ws As Worksheet)
   Dim PivotTbl As PivotTable
   On Error Resume Next
   If ws Is Nothing Then Set ws = ActiveSheet
   For Each PivotTbl In ws.PivotTables
      PivotTbl.RefreshTable
   Next PivotTbl
End Sub

then you can either keep the calling code you have, or skip the activating of each sheet and just pass them as arguments:

Sub RefreshPivotTables()
    Call RefreshActiveSheetPivotTables(Sheet1)
    Call RefreshActiveSheetPivotTables(Sheet2)
    Call RefreshActiveSheetPivotTables(Sheet3)
End Sub

 

[JeffGrant]

Hi Rory,

thanks for. I will infact impliment your suggestion because it does make the code much neater.

Just wondering though, would it make any difference to the speed of execution?

 

[RoryA]

It’s generally quicker to avoid activating and selecting things.