So I had a previous post about how I was trying to prompt a user to input a value and then based upon that input filter a pivot field on that input. And managed to come up with the following code:
[VBA] Public Sub ShowItem() 'Declare local varaiables Dim MonthSDD As String Dim ItemFound As Boolean, pvtItm
For Each pvtItm In ActiveSheet. _ PivotTables("PivotTable7"). _ PivotFields("Month (Standardized Delivery Date)").PivotItems pvtItm.Visible = True
'If a pivottable item matches the user's text, set 'ItemFound to TRUE (was previously FALSE).
If pvtItm.Value = MonthSDD Then ItemFound = True End If Next pvtItm
'If no item in the pivottable matches the user's text, 'display an error message and quit.
If ItemFound = False Then MsgBox SelItem & " Not a Valid Month in Data Range." Exit Sub End If
'Hide every item in the pivottable that does not 'match the user's text.
For Each pvtItm In ActiveSheet. _ PivotTables("PivotTable7"). _ PivotFields("Month (Standardized Delivery Date)").PivotItems If pvtItm.Value = MonthSDD Then pvtItm.Visible = True Else pvtItm.Visible = False End If Next pvtItm
End Sub
[/VBA]
But, now my question is how do I ask a user for an input 'RANGE (by month/year)' not just a single value, and then once 'RANGE (by month/year)' is entered only show that date range for the column field?
So....
Is it possible to code a macro to prompt a user for say, a date range like:
Date Range is: "Nov-09 to Nov-10"
and then have the pivot table field sort based upon that specified input?
In the attached file I need to prompt the user for the a specified date range ("Monthly Standardized Date") which is the column field on my pivot and then have the pivot generate based upon that date range entered.
Also, in most cases the seleciton will be a 12mos - 15mos range.....