PDA

View Full Version : Issue Selecting Unique Date: Data Validation List used to Filter Pivot Table Field



Kacer26
01-20-2011, 09:06 AM
Hello All:

So, I have found the following code to use a data validation box to select a "StartDate" and "EndDate" and then have the pivot table generate based upon those values.

My issue is that, the source/reference pivot range/column doesn't have a distinct/unique list of dates. Dates repeat i.e. May 06, May 06 , can show up multiple times in my list, but i need to capture the data for all instances of this month.

For instance, multiple purchase orders can be placed on the same date and so the date shows up more than once in the column. Is there a way to group the purchase orders for a particular month, so that the month instance only shows up once in the data validation list that i'm creating? :dunno

Code Below:



'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