Refreshing Pivots in multiple tabs

[FredL]

So I have this simple macro that refreshes the pivot tables in a template.
However, I have it specifying the Sheet number and Pivot Table names. The
problem comes when a user would deletes or even hides a tab or copy over a
pivot without renaming the table. Is there any way I can program it such
that it will refresh all pivot tables in a tab that is between 2 tabs.

For example:
tab 1
tab 2
tab 3
tab 4

Refresh all pivots that are between tab 1 and 4.
Then I can add tabs in between and not have to worry about Pivot Table names.

Thanks.

 

[Roger Govier]

Hi Fred

Try using
ActiveWorkbook.RefreshAll

 

[FredL]

Very nice! Simple and elegant.

Thanks!

Hi Fred

Try using
ActiveWorkbook.RefreshAll

 

[FredL]

This worked so well, that the formulas that the other pivot depends on
doesn't have a chance to get updated. So the way I have this setup is that I
have a Main Pivot table with calculations. I then have to group those
calculations together and so run a pivot of those. Since I can't do a pivot
on pivot, I have a formula that points to the results. The second pivot then
uses those formulas as its source.

With the solution below, those formulas doesn't have time to update when the
main pivot refreshes. Therefore, the second pivot still has the results from
the prior refresh. Any way around this?

 

[Roger Govier]

Hi Fred

If there is only 1 Pivot table on each of your sheets, you could use the
following code

Sub RefreshPivots()
Dim myArray As Variant, ws As Worksheet
Set myArray = Sheets(Array("Sheet2", "Sheet3", "Sheet5"))
For Each ws In myArray
ws.PivotTables(1).PivotCache.Refresh
Next
End Sub

Change the Array of Sheets to the names of your sheets, in the order you
want them calculated.

If there is more than one Pivot Table on each sheet, then use the following
code

Sub RefreshPivots2()
Dim myArray As Variant, ws As Worksheet, pt As PivotTable
Dim i As Integer, ptcount As Integer
Dim ptname As String
Set myArray = Sheets(Array("Sheet2", "Sheet3", "Sheet5"))
For Each ws In myArray
ptcount = ws.PivotTables.Count

For i = 1 To ptcount
ptname = ws.PivotTables(i).Name
ws.PivotTables(ptname).PivotCache.Refresh
Next i
Next
End Sub

The second macro will work with a single PT per sheet as well.
Again, change the array of sheet names to suit.

 

[FredL]

What if I refresh the main pivot, do a pause so that the formulas can catch
up, then refresh all; understandably, the main pivot will refresh again. Is
there a pause command?

 

[Roger Govier]

Hi

If Tools>options>Calculation>is set to Automatic, then there should be no
need for any pause, as the calculations will have been performed by the time
the next refresh occurs.
Check your setting for Calculation mode.

 

[FredL]

Hi Roger,

I do have it set to automatic. I'm using another macro to refresh the
pivots in the active worksheet.
Sub ProcedureA()
Dim PVT As PivotTable
For Each PVT In ActiveSheet.PivotTables
PVT.RefreshTable
Next PVT
End Sub

There are 5 pivots: one main one, and 4 that relies on a formula
(=IF($A17="grand total","",IF(E17="","",E17))) that refrences the main pivot.
Essentially, pivoting on a pivot. However, only one of the secondary pivots
seems to be refreshing properly. The others are refreshing but on the old
values. So the formulas doesn't have time to update before the pivot is
refreshed. Is there any way to add a .5 - 1 delay before the next pivot is
refreshed?

Thanks,
Fred

 

[ruffnro]

Fred - Did you ever get a response or find a solution to this question. I am
currently building a macro that does not work right when run full speed. If
I step through the macro, it works great. I think if I added a pause at one
point, the macro will run great full speed.

Ron

 

[ruffnro]

After I posted my other post, I looked around and found this code to put a
pause in your macro. This can be modified to remove the MsgBox. The pause
works like a champ, but did not solve my problem. I hope it solves yours.


Dim PauseTime, Start, Finish, TotalTime
If (MsgBox("Press Yes to pause for 5 seconds", 4)) = vbYes Then
PauseTime = 5 ' Set duration.
Start = Timer ' Set start time.
Do While Timer < Start + PauseTime
DoEvents ' Yield to other processes.
Loop
Finish = Timer ' Set end time.
TotalTime = Finish - Start ' Calculate total time.
MsgBox "Paused for " & TotalTime & " seconds"
Else
End
End If