Macro to hide Pivot Table items if date?

plaskettm

New Member
Joined
Feb 14, 2011
Messages
5
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.

Code:
    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
 

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result
For me the macro recorder used US style dates:

Code:
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.
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,215,777
Messages
6,126,835
Members
449,343
Latest member
DEWS2031

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top