Update PivotTable Filter by Cell Input

Question

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!

RocketDonkey RocketDonkey · Accepted Answer · 2012-08-16T20:19:34

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.

Update PivotTable Filter by Cell Input

2 Answers