PDA

View Full Version : Macro to Display Certain Items of a Pivot Field



Kacer26
01-13-2011, 09:40 AM
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:


Public Sub ShowItem()
'Declare local varaiables
Dim MonthSDD As String
Dim ItemFound As Boolean, pvtItm

'Get the text entered by the user.

MonthSDD = ActiveSheet.Range("M2").Value
ItemFound = False

'Make every item in the pivottable visible.

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


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.....

Thanks in Advance Friends! :hi: