Show only Current Contracts

[Djblois]

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?

[VBA]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[/VBA]

Please Help?

[Charlize]

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?

[vba]Sub ShowCurrent()
Dim pvtItem As PivotItem

Set pvtTable = ActiveSheet.Range("A2").PivotTable

'Hide any completed Contracts
For Each pvtItem In pvtTable.PivotFields("End Contract").PivotItems
I think that the pivotitem of the endcontract needs to be smaller than the current date to be hidden
If pvtItem > Date Then
pvtItem.Visible = False
'Debug.Print ">" & pvtItem
Else
pvtItem.Visible = True
End If
Next

Set pvtItem = Nothing
End Sub[/vba]

Please Help?

[Djblois]

Charlize I tried that and it hides everything.

[Charlize]

And this one ? [VBA]Sub pivot_contracts()
Dim pvtItem As PivotItem
' declare as date
Dim dDate As Date
' declare as long (using numbering of excel for dates)
Dim lDate As Long
' store today in datevariabele
dDate = DateSerial(Year(Now), Month(Now), Day(Now))
' save as number
lDate = dDate
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 < lDate 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 < lDate Then
pvtItem.Visible = False
'Debug.Print ">" & pvtItem
Else
pvtItem.Visible = True
End If
Next
Set pvtItem = Nothing
End Sub[/VBA]