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
'================
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Back
Top