Macro to hide Pivot Table items if date?

[plaskettm]

Hello, I've been racking my brains for 2 days on this so hopefully someone can help. Using Excel 2010, I have created a basic pivottable where one of the Pivot Fields ("month") is a date field. I've recorded the below macro to hide specific pivot items in the Month field but when I run it it fails to find the first (or any) of the Pivot items.

    With ActiveSheet.PivotTables("PivotTable9").PivotFields("Month")
        .PivotItems("01/06/2011").Visible = False
        .PivotItems("01/09/2011").Visible = False
        .PivotItems("01/10/2011").Visible = False
    End With

I've tried change the formatting of the source data, and the above code to "06/01/2011", "6/1/2011" all to no avail.

This does work OK in excel 2003 - please help

 

[Andrew Poulsom]

For me the macro recorder used US style dates:

Sub Macro1()
'
' Macro1 Macro
'
    With ActiveSheet.PivotTables("PivotTable9").PivotFields("Month")
        .PivotItems("6/1/2011").Visible = False
        .PivotItems("9/1/2011").Visible = False
        .PivotItems("10/1/2011").Visible = False
    End With
End Sub

I am in the UK and I could replay the macro.

 

[plaskettm]

Hello and thank you for response. I'd read that the date would be american format on a few posts but didn't work for myself, until I removed the blank field items - then it worked.

I guess excel was somehow getting confused trying to convert a blank field to an american date format?

Anyway, I had no need for the blank fields so it's now fixed (wish I'f thought of that 2 days ago)(

Thanks