CODEWITHSUNDEEP
excelvba
Rodrigo Wiggers
Rodrigo Wiggers

Reputation: 25

Refresh pivots doenst appear error

Here is my code:

Sub Atualiza_pivots()

ActiveWorkbook.RefreshAll

End Sub

I do simple code that refresh all the pivots in the workbook, but if some pivot doenst refresh, it doesnt appear to me, so, I dont know when its getting a error. I try to make another code but it keep doesnt appear.

Sub Atualiza_pivots()

On Error GoTo Err

ActiveWorkbook.RefreshAll

Exit Sub

Err: MsgBox "Há pivots com erro, verifique."

End Sub

Thank you.

Upvotes: 1

Views: 117

Answers (2)

Rodrigo Wiggers
Rodrigo Wiggers

Reputation: 25

Thx for the help D_Zab

Sub Atualiza_pivots()

Dim wks As Worksheet
Dim pvt As PivotTable

For Each wks In Worksheets
    For Each pvt In wks.PivotTables
            On Error GoTo Err
            pvt.PivotCache.Refresh
    Next pvt
Next wks
Exit Sub
Err: MsgBox pvt & " com erro."
End Sub

Upvotes: 0

D_Zab
D_Zab

Reputation: 735

The RefreshAll method only works if the BackgroundQuery is set to True, try this to loop through each table and refresh it manually:

Sub Refresher()

Dim wks As Worksheet
Dim pvt As PivotTable

For Each wks In Worksheets
    For Each pvt In wks.PivotTables
        If pvt.PivotCache.BackgroundQuery = False Then
            pvt.PivotCache.BackgroundQuery = True
            pvt.RefreshTable
            pvt.PivotCache.BackgroundQuery = False
        Else
            pvt.RefreshTable
        End If
    Next pvt
Next wks

End Sub

Upvotes: 2

Related Questions