PDA

View Full Version : Show only Current Contracts



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?

Charlize
06-22-2007, 06:57 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 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

Please Help?

Djblois
06-22-2007, 07:35 AM
Charlize I tried that and it hides everything.

Charlize
06-24-2007, 03:32 PM
And this one ? 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