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.

This page shows a technique for comparing NOW() to the .PivotItems and hiding the ones that result in "false" on the test.

Hide Show Pivot Column Items Based on Date - Excel Help Best Practices Forums

Code:

---

Sub Macro1()
    '
    Dim pvtItem As PivotItem
    Dim strDate As String
   
    ' check  date  format - especially single digit days 01 or 1
   
    strDate = Format(Now(), "mm/dd/yyyy")
    With ActiveSheet. PivotTables("PivotTable1").PivotFields("Date")
        .ShowAllItems = True '
        For Each pvtItem In .PivotItems
            pvtItem.Visible = (pvtItem. Name = strDate)
        Next
    End With
End Sub

---

You could put that date to filter by in a CELL (A1? ) and adjust the formula to be greater than instead of equal:

Code:

---

Sub Macro1()
    '
    Dim pvtItem As PivotItem
    Dim strDate As String
   
    ' check  date  format - especially single digit days 01 or 1
   
    strDate = Format(Range("A1").Value, "m/d/yyyy")
    With ActiveSheet. PivotTables("PivotTable1").PivotFields("Date")
        .ShowAllItems = True '
        For Each pvtItem In .PivotItems
            pvtItem.Visible > (pvtItem. Name = strDate)
        Next
    End With
End Sub

---

This should give you enough to "fiddle with".

Quote:

---

Originally Posted by JBeaucaire

This page shows a technique for comparing NOW() to a the .PivotItems and hiding the ones that result in "false" on the test.

Hide Show Pivot Column Items Based on Date - Excel Help Best Practices Forums

Code:

---

Sub Macro1()
    '
    Dim pvtItem As PivotItem
    Dim strDate As String
   
    ' check  date  format - especially single digit days 01 or 1
   
    strDate = Format(Now(), "mm/dd/yyyy")
    With ActiveSheet. PivotTables("PivotTable1").PivotFields("Date")
        .ShowAllItems = True '
        For Each pvtItem In .PivotItems
            pvtItem.Visible = (pvtItem. Name = strDate)
        Next
    End With
End Sub

---

You could put that date to filter by in a CELL (A1 ??) and adjust the formula to be greater than instead of equal:

Code:

---

Sub Macro1()
    '
    Dim pvtItem As PivotItem
    Dim strDate As String
   
    ' check  date  format - especially single digit days 01 or 1
   
    strDate = Format(Range("A1").Value, "m/d/yyyy")
    With ActiveSheet. PivotTables("PivotTable1").PivotFields("Date")
        .ShowAllItems = True '
        For Each pvtItem In .PivotItems
            pvtItem.Visible > (pvtItem. Name = strDate)
        Next
    End With
End Sub

---

This should give you enough to "fiddle with".

---

Thanks, guy... I've give you a "greenie" but I got to spread some of the love around. I think this will work so I will definitely "fiddle with" it.