Phelony
10-18-2012, 05:01 AM
Hi guys
I'm trying to make a quick filter for a pivot table that will show only the items between today and the number of days specified in the input box. :dunno
Private Sub CommandButton1_Click()
Dim pvtItem As PivotItem
Dim strDate As Date
Dim pTable As String
Dim pField As String
Dim dRange As Long
'sheet variables
pTable = "PivotTable5"
pField = "Date"
dRange = InputBox("Enter the number of days ahead of today you would like to view as a number. (e.g 6 months = 180)")
strDate = Format(Now(), "dd/mm/yyyy")
With ActiveSheet.PivotTables(pTable).PivotFields(pField)
.ShowAllItems = True '
For Each pvtItem In .PivotItems
'should show dates between today and today + the number entered in the input box
pvtItem.Visible = (pvtItem.Name > strDate < (Now() + dRange))
Next
End With
End Sub
It's pretty basic, but I've scowered the net looking for how to make a "greater than but less than" operator and haven't had much luck. :help
If anyone could help it would be greatly appreciated.
Thanks
Phel
I'm trying to make a quick filter for a pivot table that will show only the items between today and the number of days specified in the input box. :dunno
Private Sub CommandButton1_Click()
Dim pvtItem As PivotItem
Dim strDate As Date
Dim pTable As String
Dim pField As String
Dim dRange As Long
'sheet variables
pTable = "PivotTable5"
pField = "Date"
dRange = InputBox("Enter the number of days ahead of today you would like to view as a number. (e.g 6 months = 180)")
strDate = Format(Now(), "dd/mm/yyyy")
With ActiveSheet.PivotTables(pTable).PivotFields(pField)
.ShowAllItems = True '
For Each pvtItem In .PivotItems
'should show dates between today and today + the number entered in the input box
pvtItem.Visible = (pvtItem.Name > strDate < (Now() + dRange))
Next
End With
End Sub
It's pretty basic, but I've scowered the net looking for how to make a "greater than but less than" operator and haven't had much luck. :help
If anyone could help it would be greatly appreciated.
Thanks
Phel