Runtime Error 1004: Unable to get the PivotTables property of the Worksheet class

Question

I recorded this macro to update the date range of 16 charts. But, as you can see I am getting a Run-time error.

I have looked at other threads on stackoverflow that relate to this but none come close. The darn help button on excel doesn't help either. Can you please advise? Here is the code:

ActiveSheet.ChartObjects("Chart 18").Activate
ActiveChart.Axes(xlCategory).Select
ActiveSheet.ChartObjects("Chart 18").Activate
With ActiveSheet.PivotTables("PivotTable2").PivotFields("Date")
    .PivotItems("Sep-15").Visible = False
    .PivotItems("Aug-14").Visible = True
End With

Solution

Here's some simple code that should get you close. You will need to change "MySheet" in this code to the name of the sheet containing the pivot table in your workbook, and your Date field must truly be text formatted in the "Mmm-YY" format.

Sub ShowThirteenDatesStartingLastMonth()
    Sheets("MySheet").PivotTables("PivotTable2").PivotCache.Refresh

    Dim Dt As String

    With Sheets("MySheet").PivotTables("PivotTable2").PivotFields("Date")        
    For h = 1 To .PivotItems.Count - 1
        On Error Resume Next
        .PivotItems(h).Visible = False
    Next h

    For i = 1 To 13
        On Error Resume Next
        Dt = Format(DateSerial(Year(Date), Month(Date) - i, 1), "Mmm-YY")
        .PivotItems(Dt).Visible = True
    Next i
    End With
End Sub