Hiding PivotItems using VBA
I have built a simple macro using VBA which hides certain dates from view in my pivot table. For example, I have date values like: 1/1/2007, 1/5/2007, 2/15/2007, 5/1/2008, 2/1/2009. I've been able create my macro to hide dates that I specify in the below code:
Sub Macro1()
With ActiveSheet.PivotTables("PivotTable1").PivotFields ("Date")
.PivotItems("1/2/2007").Visible = False
.PivotItems("2/15/2007").Visible = False
.PivotItems("5/1/2008").Visible = False
End With
End Sub
My problem is that sometimes I have to refresh the data behind the pivot table and the dates can change but I will want to exclude certain dates from appearing in my pivot table results. For instance, I might want to exclude all dates prior to February 2007 or see only those dates that appear after January 2009. I can go into the pivot table and just de-select the dates but it's very tedious.
Can I create code that will de-select all dates prior to a given period? For example, if I had dates throughout January and February 2009 and I only wanted to see February dates, can I make the code do something like hide all date values <2/1/2009? Is that possible without having to go into the pivottable and manually de-select all January dates?
Hope this makes sense.