Pivot Table Field Lookup

J

Josh Johansen

Is it possible to type a value in a cell that would then be referenced by a
field in a pivot table and then lookup and select that value instead of using
the drop down box and having to search through the entire list to find the
value necessary. Thanks.
 
J

Josh Johansen

roger, I appologize, somehow I marked that question answered and so I was
filtering it out, I appologize. Thanks for the previous answer, I am going
to work on it now, sorry for the inconvenience.
 
J

Josh Johansen

Roger, I copied the program you left yesterday and nothing happened. The
field I am attempting to use is actually a page selection, but I moved it
down into a row area and tried the same thing and nothing happened. I am
using F1 as the cell to type in the value I would like the pivot table field
to select, i didnt change the pivot table field, and I did change the pivot
table field name to the same as the one I am trying to change. I am not sure
what I have done wrong.
 
R

Roger Govier

Hi Josh

Where did you paste the code?
It is Worksheet_Change event and must be pasted onto the Sheet with the
PT, not into a module that has been inserted in the workbook.

Also, you may have got through to the point of
Application.EnableEvents = False

and crashed.

In the immediate Windows of the VBE type
Application.EnableEvents = True and press Enter

It worked fine for me when I tested it.
 
J

Josh Johansen

I pasted the code by right clicking on the sheet with the PT and going to
view code. I am getting the following error:

Run-time error '1004':

Unable to set the Visible property of the PivotItem class

also I am not sure what you mean by in the immediate windows of the VBE type
Application.EnableEvents = True and press Enter

Is it not working because I have a command button and a popup calander
running on the same sheet?
 
J

Josh Johansen

when i go to bebug, it highlights the pi.Visible = True row, here is
everything after that.

pi.Visible = True
Next
For Each pi In pf.PivotItems
If pi.Name <> Range("F1").Value Then
pi.Visible = False
End If
Next
pf.AutoSort xlAutomatic, pf.SourceName

End With

Application.ScreenUpdating = False
Application.EnableEvents = True
End Sub
 
Top