Pivot Table Filter by User Entry

[Jayco]

I'm attempting to create a single Pivot Table that automatically
applies a filter, depending on what the user enters into a form. For
example, salesperson "John" opens the excel file and enters his name
into a pop-up form. Then a Pivot is displayed with only John's
sales--filtered by selecting John's salesperson number in the Pivot's
filter.

Does anyone know how to do this?? Help would be greatly appreciated!

 

[Debra Dalgleish]

You can use programming to set the page field. For example:

'==============
Sub FilterPivot()
Dim pt As PivotTable
Dim pf As PivotField
Dim pi As PivotItem
Dim strEmp As String

strEmp = InputBox("Enter your Last Name", "Last Name")
Set pt = ActiveSheet.PivotTables(1)
Set pf = pt.PageFields("Employee")

For Each pi In pf.PivotItems
If LCase(pi.Value) = LCase(strEmp) Then
pf.CurrentPage = pi.Value
Exit Sub
End If
Next pi

End Sub
'================