VBA Refresh All Pivot Tables

[Mudbutt]

Is there a difference between these two procedures to refresh all pivot tables on a workbook? I've been finding that not all my pivots have been refreshing with this first option so I was going to try the second one I have here. Any ideas?

#1

For Each ws In Activeworkbook.Worksheets
        For Each pt In ws.PivotTables
            pt.RefreshTable
        Next pt
    Next ws

#2

Activeworkbook.RefreshAll

 

[Jerry Sullivan]

I believe those should both achieve the same end result.

The version #2 is probably more efficient. In addition to not stepping through each sheet, it probably updates each PivotCache once, whereas if any PivotTables share the same PivotCaches, those PivotCaches will get refreshed more than once with version #1.

Is it possible there is some other reason your PivotTables don't appear to be refreshing, such as a change in the size of the data range you are trying to use as your Pivot Data source?

 

[Mudbutt]

There is something that is holding me back from my code properly refreshing the tables. It's not the ranges because i just have them set to columns, and the only way I've been able to refresh the tables is to manually hit the "Refresh All" button on the data tab...however it doesn't do it for me within my code.

This doesn't make a bit of sense to me because I use this refresh code in other reports and it works, I just have a couple others that won't work. And it's not like it's a shared file, or that the sheets are locked or something because manually refreshing them works...

 

[Jerry Sullivan]

There is something that is holding me back from my code properly refreshing the tables. It's not the ranges because i just have them set to columns, and the only way I've been able to refresh the tables is to manually hit the "Refresh All" button on the data tab...however it doesn't do it for me within my code.

This doesn't make a bit of sense to me because I use this refresh code in other reports and it works, I just have a couple others that won't work. And it's not like it's a shared file, or that the sheets are locked or something because manually refreshing them works...

That is strange. I haven't I've encountered a scenario in which the result of VBA yields a different result than the same function applied through an Excel button.

Is your PivotData source external to your workbook with the PivotTables?
Do the same PivotTables consistently succeed or fail using this code or is it hit and miss?

 

[Mudbutt]

All these pivot tables are linked internally, where I'm updating sheets that thousands of formulas are connected to. Do you think it's possible that when I'm pasting in the new information that it just hasn't had time to calculate and properly update all the formulas before my refreshall code starts running? Is there a way around that?

 

[RoryA]

I don't imagine it will make any difference if those two methods don't work, but I tend to use:

Sub RefreshAllPivots()
   Dim PC As PivotCache
   For Each PC In ActiveWorkbook.PivotCaches
      PC.Refresh
   Next PC
End Sub

 

[Jerry Sullivan]

All these pivot tables are linked internally, where I'm updating sheets that thousands of formulas are connected to. Do you think it's possible that when I'm pasting in the new information that it just hasn't had time to calculate and properly update all the formulas before my refreshall code starts running? Is there a way around that?

I'm not positive, but unless you have Calculation set to manual, that seems unlikely to me.

I asked this question...
Do the same PivotTables consistently succeed or fail using this code or is it hit and miss?

...because if the failure is consistent, you can take a copy of your workbook an use a divide-and-conquer approach of deleting parts of your workbook until you isolate which change makes it start to work.

If your VBA works sometimes-but not others on the same PivotTable setup- then this method wouldn't work very well.

 

[Jerry Sullivan]

Setting aside figuring out the mystery of why this is happening with your code...

you might have better results in general if your PivotTable references a data source that is a Table (Listobject) or Named range instead of using the entire columns.

 

[Mudbutt]

Yeah, maybe I could try that. It seems there are always two tables that don't update within this workbook, but all the other ones do...However, if I manually hit refreshall it will update.

 

[goochspaintball]

Is there a difference between these two procedures to refresh all pivot tables on a workbook? I've been finding that not all my pivots have been refreshing with this first option so I was going to try the second one I have here. Any ideas?

#1

For Each ws In Activeworkbook.Worksheets
        For Each pt In ws.PivotTables
            pt.RefreshTable
        Next pt
    Next ws

#2

Activeworkbook.RefreshAll

I can't get the first option to even work (nothing changes and no errors prompted) in excel 2010...the "RefreshAll" option seems to be the only way to do it, unless I have some setting wrong for macros.

The problem with the refresh all option is that it updates tables with ODBCs that you may not necessarily want to update every time you run the macro. It just seems silly for the old vba not to work anymore.