asked on

Pivot tables not refreshing by VBA

Im using the below vba code - to resfresh all pivots

Sub refreshpivot()
For ishtCount = 1 To Sheets.Count
For ipiv = 1 To Sheets(ishtCount).PivotTables.Count

Application.Wait Now() + TimeValue("00:00:01")

If Sheets(ishtCount).PivotTables(ipiv).Name <> "RETAIL Book" Then
Sheets(ishtCount).PivotTables(ipiv).RefreshTable

End If
DoEvents
Next ipiv
Next ishtCount

Application.Calculate
MsgBox "Done"
End Sub

Works fine sometimes other times I get the error message Run-time error '1004'

Refresh tabke method of PivotTable class failed.

jppinto

Instead of this:

Sheets(ishtCount).PivotTables(ipiv).RefreshTable

try like this:

Sheets(ishtCount).PivotTables(ipiv).PivotCache.Refresh

jppinto

jppinto

Or try this different approach:

Sub refreshpivot()

Dim pt As PivotTable
Dim ws As Worksheet

    For Each ws In ActiveWorkbook.Worksheets
        Application.Wait Now() + TimeValue("00:00:01")
        For Each pt In ws.PivotTables
            If pt.Name <> "RETAIL Book" Then
                pt.RefreshTable
            End If
        Next pt
    Next ws
    Application.Calculate
    MsgBox "Done"
    
End Sub

Open in new window

jppinto

ASKER CERTIFIED SOLUTION

Steve

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial