CODEWITHSUNDEEP
vbaexcel
Yassine
Yassine

Reputation: 5

Refresh all pivot tables in file

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

Upvotes: 0

Views: 8056

Answers (4)

mherzog
mherzog

Reputation: 1230

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.

Upvotes: 0

pbeentje
pbeentje

Reputation: 313

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

Upvotes: 0

Claes
Claes

Reputation: 1

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

ActiveWorkbook.RefreshAll

Upvotes: 0

Giovanni Russo
Giovanni Russo

Reputation: 273

Try this code:

Sub AllWorkbookPivots()
    ActiveWorkbook.RefreshAll()
End Sub

Upvotes: 3

Related Questions