VBA PivotTables "Unable to get the PivotItems of the PivotField Class"

[prks21]

Hey Guys,

I've been trying to solve this for most of the day and is really annoying. Please help!

I've been writing a macro to select an additional date in a date filter of a pivot table. I recorded the macro, as such:

Sub Macro4()
'
' Macro4 Macro
'
'
    ActiveSheet.PivotTables("PivotTable2").PivotFields("Order_Date").CurrentPage = _
        "(All)"
    With ActiveSheet.PivotTables("PivotTable2").PivotFields("Order_Date")
        .PivotItems("5/02/2015").Visible = True
    End With
End Sub

However, even when I try running that code I get an error. how is that even possible? Recorded macro not performing what it did....

Any help would be much appreciated. Thanks!

[Bob Phillips]

It is VBA's problem with dates, it is US centric, so records US centric but yours dates in Excel are probably not (which is why it records okay, but then doesn't run).

What you need to do is force a date formatted as you have in the pivot field. In the example I show below, my date is 2nd May, and my format is d-mmm, you will need to change as per your values

    With ActiveSheet.PivotTables("PivotTable2")
    
        With .PivotFields("Order_Date")
        
            .PivotItems(Format(DateSerial(2015, 5, 2), "d-mmm")).Visible = True
        End With
    End With

I did try to pick up the field's Numberformat property, there is one, but it seems to be indeterminate.

[prks21]

It is VBA's problem with dates, it is US centric, so records US centric but yours dates in Excel are probably not (which is why it records okay, but then doesn't run).

What you need to do is force a date formatted as you have in the pivot field. In the example I show below, my date is 2nd May, and my format is d-mmm, you will need to change as per your values

    With ActiveSheet.PivotTables("PivotTable2")
    
        With .PivotFields("Order_Date")
        
            .PivotItems(Format(DateSerial(2015, 5, 2), "d-mmm")).Visible = True
        End With
    End With

I did try to pick up the field's Numberformat property, there is one, but it seems to be indeterminate.

I ran your code (modified date format to "dd/mm/yyyy" and it gives the same error. Not sure why it is playing up.