Djblois
06-22-2007, 06:21 AM
I created this code to exclude any items in a Pivot table whos Start Date Didn't start yet or End Date Ended Already. However, if the Start Date is last year it is hiding the row. I can't figure it out?
Sub ShowCurrent()
Dim pvtItem As PivotItem
Set pvtTable = ActiveSheet.Range("A2").PivotTable
'Hide any contracts that have not begun yet
For Each pvtItem In pvtTable.PivotFields("Begin Contract").PivotItems
If pvtItem < Year(Date) Then
pvtItem.Visible = True
'Debug.Print "<" & pvtItem
ElseIf pvtItem < Date Then
pvtItem.Visible = True
Else
pvtItem.Visible = False
End If
Next
'Hide any completed Contracts
For Each pvtItem In pvtTable.PivotFields("End Contract").PivotItems
If pvtItem > Date Then
pvtItem.Visible = False
'Debug.Print ">" & pvtItem
Else
pvtItem.Visible = True
End If
Next
Set pvtItem = Nothing
End Sub
Please Help?
Sub ShowCurrent()
Dim pvtItem As PivotItem
Set pvtTable = ActiveSheet.Range("A2").PivotTable
'Hide any contracts that have not begun yet
For Each pvtItem In pvtTable.PivotFields("Begin Contract").PivotItems
If pvtItem < Year(Date) Then
pvtItem.Visible = True
'Debug.Print "<" & pvtItem
ElseIf pvtItem < Date Then
pvtItem.Visible = True
Else
pvtItem.Visible = False
End If
Next
'Hide any completed Contracts
For Each pvtItem In pvtTable.PivotFields("End Contract").PivotItems
If pvtItem > Date Then
pvtItem.Visible = False
'Debug.Print ">" & pvtItem
Else
pvtItem.Visible = True
End If
Next
Set pvtItem = Nothing
End Sub
Please Help?