PDA

View Full Version : Issue Selection Data Validation List used to Filter Pivot Table Field



Kacer26
01-20-2011, 08:59 AM
Hello All!

So, I am trying to use the Data/Validation functionality in Excel.

I have a Data Validaiton box set up to prompt user for "StartDate" and "EndDate" from Data Validation list.

My issue, is that the reference/source pivot column where I am



'Code modified from blog.contextures.com

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("TestSheet").Range("StartDate").Value
dEnd = Sheets("TestSheet").Range("EndDate").Value
Set pt = ActiveSheet.PivotTables("PivotTable1")
Set pf = pt.PivotFields("Month (Standardized Delivery Date)")
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