Reputation: 137
I am trying to update a filter in my pivot table (date) from 2 cell input (startdate, enddate), so that I set a startdate (e.g. 01/01/12) and an enddate (28/03/12) the pivot table will automatically apply a filter displaying just the dates within the range. For this purpose, I found some VBA code snippets on the internet:
Option Explicit
Sub FilterPivotDates()
'
Dim dStart As Date
Dim dEnd As Date
Dim pt As PivotTable
Dim pf As PivotField
Dim pi As PivotItem
Application.ScreenUpdating = False
On Error Resume Next
dStart = Sheets("Pivot").Range("StartDate").Value
dEnd = Sheets("Pivot").Range("EndDate").Value
Set pt = ActiveSheet.PivotTable2
Set pf = pt.PivotFields("Week")
pt.ManualUpdate = True
pf.EnableMultiplePageItems = True
For Each pi In pf.PivotItems
pi.Visible = True
Next pi
For Each pi In pf.PivotItems
If pi.Value < dStart Or pi.Value > dEnd Then
pi.Visible = False
End If
Next pi
Application.ScreenUpdating = False
pt.ManualUpdate = False
Set pf = Nothing
Set pt = Nothing
End Sub
I have that in the Modules folder of my worksheet and in Sheet "Pivot" I put "Option Explicit". I assigned the range names "StartDate" and "EndDate" to the two cells with date input and assigned a button with the macro. The PivotTable is named "PivotTable" and the respective Field that should be filtered is named "Week". But when I hit the button nothing happens... Does someone see a error in the code or should I implement it differently?
Thanks upfront!
Upvotes: 0
Views: 1901
Reputation: 2265
My guess is that your problem is in your dstart and dend. By getting the value I believe it changes the date to the excel numerical value.
try changing this line:
If pi.Value < dStart Or pi.Value > dEnd Then
to something like:
If cdate(pi.value)< cdate(dStart) Or cdate(pi.value) > cdate(dend) then
if that doesn't work try only putting the cdate on your two created variables
Upvotes: 0
Reputation: 37249
Try changing the final
Application.ScreenUpdating = False
to
Application.ScreenUpdating = True
It could be that you did everything properly but just can't see it :) If that isn't the case (which it sounds like it wasn't), it could be that you aren't comparing similar types. This isn't an answer, but it may help figure out what is wrong: try placing some debug statements for the loop to determine the values/types in the input cells:
Debug.Print "dStart Value: " & dStart.Value
Debug.Print "dEnd Value: " & dEnd.Value
Debug.Print "dStart Type: " & VarType(dStart)
Debug.Print "dEnd Type: " & VarType(dEnd)
And then adding another check into your loop:
For Each Pi In pf.PivotItems
Debug.Print "Pi Value: " & Pi.Value
Debug.Print "Pi Type: " & VarType(Pi.Value)
If Pi.Value < dStart Or Pi.Value > dEnd Then
Pi.Visible = False
End If
Next Pi
Again, not sure if this will reveal anything, but it could be that the code is working but not comparing anything. On that note, have you tried modifying the loop to verify that it is working by hard-coding a value to select? If that works, we can narrow it down a bit more.
Upvotes: 1