Pivot Table Filter by User Entry

  • Thread starter Thread starter Jayco
  • Start date Start date
J

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!
 
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
'================
 
Back
Top