VBA Refresh All Pivot Tables

Mudbutt

Board Regular
Joined
Jul 18, 2011
Messages
158
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
Code:
For Each ws In Activeworkbook.Worksheets
        For Each pt In ws.PivotTables
            pt.RefreshTable
        Next pt
    Next ws
#2
Code:
Activeworkbook.RefreshAll
 

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop
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?
 
Upvote 0
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...
 
Upvote 0
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?
 
Upvote 0
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?
 
Upvote 0
I don't imagine it will make any difference if those two methods don't work, but I tend to use:
Code:
Sub RefreshAllPivots()
   Dim PC As PivotCache
   For Each PC In ActiveWorkbook.PivotCaches
      PC.Refresh
   Next PC
End Sub
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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
Code:
For Each ws In Activeworkbook.Worksheets
        For Each pt In ws.PivotTables
            pt.RefreshTable
        Next pt
    Next ws
#2
Code:
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.
 
Upvote 0

Forum statistics

Threads
1,213,543
Messages
6,114,236
Members
448,555
Latest member
RobertJones1986

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top