Excel VBA/button to refresh two or more pivots simultaneously

Question

In another post I was provided with the following VBA code to attach to a "Refresh" button to refresh Pivot data:

Option Explicit

Sub Button5_Click()

Dim PvtTbl                  As PivotTable
Dim PvtCache                As PivotCache
Dim PvtDataRng              As Range

' Set/Update Pivot Data Range
Set PvtDataRng = Worksheets("PivotDataSheet").Range("A1:E100") ' <-- just an example

' Create/Update Pivot Cache
Set PvtCache = ActiveWorkbook.PivotCaches.Add(xlDatabase, PvtDataRng)

' Set the Pivot Table (already created, otherwise need to create it)
Set PvtTbl = Worksheets("DD").PivotTables("test")

' refresh the Pivot Table with the latest Pivot Cache
With PvtTbl
    .ChangePivotCache PvtCache
    .RefreshTable
End With

End Sub

How can this code be modified to simultaneously refresh a 2nd pivot in the same worksheet? Lets call this 2nd pivot "test2" and it is also in the DD worksheet.

Thanks, Kevbo

Just repeat the steps of the code you've been given with the other pivot table name... — Jordan, Mar 14 '17 at 09:03
Does the second pivot table named "test2" has the same source of data as the first Pivot Table ? — Shai Rado, Mar 14 '17 at 11:42
Yes they are using the same data source. Wont adding a duplicate of this code overwrite the definition of Set PvtTbl =? — Kevbo, Mar 14 '17 at 13:25

Shai Rado · Accepted Answer · 2017-03-14T15:24:51.437

In case you have only these 2 PivotTable in "DD" worksheet, and you want to update these 2 based on the same PivotCache, then replace the following For loop :

With PvtTbl
    .ChangePivotCache PvtCache
    .RefreshTable
End With

With the following For loop:

' refresh all Pivot Tables in "DD" worksheet
For Each PvtTbl In Worksheets("DD").PivotTables
    With PvtTbl
        .ChangePivotCache PvtCache
        .RefreshTable
    End With
Next PvtTbl

Edit 1: Set the PivotCache inside the For loop, and clear it at the end. use the loop below, and remove the Set PvtCache line from it's previous place.

' refresh all Pivot Tables in "DD" worksheet
For Each PvtTbl In Worksheets("DD").PivotTables
    ' Create/Update Pivot Cache
    Set PvtCache = ActiveWorkbook.PivotCaches.Add(xlDatabase, PvtDataRng)

    With PvtTbl
        .ChangePivotCache PvtCache
        .RefreshTable
    End With

    Set PvtCache = Nothing ' <-- clear the Pivot Cache
Next PvtTbl

Almost there... Received runtime Error 5: "Invalid Procure call or argument" on the line: '.ChangePivotCache PvtCache' — Kevbo, Mar 14 '17 at 15:02
@Kevbo did it fail on the first `PivotTable` ? or the second ? — Shai Rado, Mar 14 '17 at 15:04

score 0 · Answer 2 · answered Mar 14 '17 at 09:07

0

I haven't tested this myself but you could try and tell me if it works. It seems like it may work for you

Function refreshPTcontent()

Dim ws As Worksheet
Dim pt As PivotTable
Dim pf As PivotField
Dim pi As PivotItem

On Error Resume Next
    For Each ws In ActiveWorkbook.Worksheets
       For Each pt In ws.PivotTables
         pt.RefreshTable
       Next pt
    Next ws

End Function

answered Mar 14 '17 at 09:07

Martin Router King

155
1
13

Not sure if I used it correctly, but I added that code to existing macro. It didnt refresh the 2nd pivot. – Kevbo Mar 14 '17 at 13:37
Sorry about that, I wasn't sure if it was gonna work. try with `ActiveWorkbook.RefreshAll` this might be slow tho. Source: https://www.thespreadsheetguru.com/blog/2014/9/27/vba-guide-excel-pivot-tables – Martin Router King Mar 14 '17 at 13:49
Check this one too... http://stackoverflow.com/questions/70947/how-can-i-refresh-all-the-pivot-tables-in-my-excel-workbook-with-a-macro – Martin Router King Mar 14 '17 at 13:54

Excel VBA/button to refresh two or more pivots simultaneously

2 Answers2