VBA code for Pivot Table results in "Runtime Error 91: Object variable or with block variable not set"

[jdbienenfeld]

I am trying to build a macro to refresh multiple pivot tables in a loop without having to use the name of the pivot table "PivotTable1", etc.. I found this code:

Sub UpdateWorkStreamIssues_LOOP()
'
Sheets("Ambulatory").Select

' UpdateWSIssues Macro LOOP
Dim pt As PivotTable
For i = 1 To 2
ActiveSheet.Select
Range("A1").Select
pt.RefreshTable
Sheets(ActiveSheet.Index + 1).Activate
Next

End Sub


I get this error: "RuntimeError 91: Object variable or with block variable not set" for the code "pt.RefreshTable"

What am I doing wrong?

 

[MARK858]

Maybe...

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

    For Each ws In ActiveWorkbook.Worksheets
        On Error Resume Next
        For Each pt In ws.PivotTables
            pt.RefreshTable
        Next pt
        On Error GoTo 0
    Next ws
End Sub

or even maybe..

Sub RefreshPts2()
    ActiveWorkbook.RefreshAll
End Sub