Refresh all pivot tables in file

Question

When I run this code in Excel 2010 I get the error message:

1004 refresh table method of pivot table class failed

Sub AllWorkbookPivots()
    Dim pt As PivotTable
    Dim ws As Worksheet

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

End Sub

Possible duplicate of http://stackoverflow.com/questions/70947/how-can-i-refresh-all-the-pivot-tables-in-my-excel-workbook-with-a-macro — Matt Roy, Mar 01 '17 at 21:52

Giovanni Russo · Accepted Answer · 2016-11-24T15:37:29.203

3

Try this code:

Sub AllWorkbookPivots()
    ActiveWorkbook.RefreshAll()
End Sub

edited Nov 24 '16 at 15:37

answered Nov 24 '16 at 15:36

Giovanni Russo

273
2
7

score 0 · Answer 2 · edited Oct 29 '20 at 21:08

0

When I tried this, I had to remove the parenthesis.

ActiveWorkbook.RefreshAll

edited Oct 29 '20 at 21:08

Tamás Sengel

55,884
29
169
223

answered Oct 29 '20 at 09:07

Claes

1

score 0 · Answer 3 · answered Apr 16 '21 at 08:42

A pivot refresh will fail if after refreshing any of the pivot tables that use that pivot cache would overlap another pivot table object or other bounded object. Excel will try to create extra rows and columns to make space for the resized table, but this is not always straightforward.

In that case, RefreshAll will also fail to update the table. Check all sheets that contain a pivot table, and try to ensure each pivot table has space to expand if needed.

If you're not sure which table uses which cache, you can use the following script:

Sub PivotInfo()
    Dim i As Integer, wSheet As Worksheet, pTable As PivotTable    
    Worksheets.Add
    Range("A1") = "Pivot table name"
    Range("B1") = "Location"
    Range("C1") = "Source information"

    i = 1

    For Each wSheet In Worksheets
        For Each pTable In wSheet.PivotTables
            i = i + 1
            Cells(i, 1).Value = pTable.Name
            Cells(i, 2).Value = wSheet.Name + "!" + pTable.TableRange1.Address
            Cells(i, 3).Value = pTable.SourceData
        Next pTable
    Next wSheet
End Sub

score 0 · Answer 4 · answered Oct 25 '21 at 23:39

I got errors refreshing my pivot tables until I stopped allowing refreshes to run in the background:

Fails intermittently:

Dim t, ws, pc

For Each ws In ThisWorkbook.Worksheets
    For Each t In ws.PivotTables
        Debug.Print "Refreshing " & t.Name
        Set pc = t.PivotCache
        pc.Refresh
    Next
Next

Seems to work fine:

Dim t, ws, pc

For Each ws In ThisWorkbook.Worksheets
    For Each t In ws.PivotTables
        Debug.Print "Refreshing " & t.Name
        Set pc = t.PivotCache
        pc.BackgroundQuery = False    '<==== Make queries execute in the foreground
        pc.Refresh
    Next
Next

I noticed that without setting pc.BackgroundQuery, this value was True. My theory is that before the first refresh finishes, the second one begins, and since they are two tables referring to the same cache, the second one fails.

Refresh all pivot tables in file

4 Answers4