Change a pivot table field by typing into another cell

[Guest]

I would like to be able to change a field selection in a row area of my pivot
table by typing into another cell outside of the pivot table. There are so
many selections in the field that it would be much easier for the user to be
able to type what they would like the select in that field for sorting.
Thanks.

 

[Roger Govier]

Hi Josh

If you add this code to the worksheet containing the PT, then as you
change the value entered in cell F1, the PT will react with a change in
the Row field selection to that value

I chose F1 to hold the value. If you change it to a different cell, then
change the value of Target.Row and Target.Column as appropriate in the
first IF test.

Also ensure the name of the Pivot Table agrees with yours, and set the
value of myField to the name of your row field you are wanting to
change.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Row <> 1 Or Target.Column <> 6 Then Exit Sub
Dim pi As PivotItem, pf As PivotField
Application.EnableEvents = False
Application.ScreenUpdating = False
' change name of Pivto Table and Pivot Field to your values on next
line
Set pf = ActiveSheet.PivotTables("PivotTable1").PivotFields("myfield")
With pf
pf.AutoSort xlManual, pf.SourceName

For Each pi In pf.PivotItems
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

To use this code, right click on the sheet tab with your PT>View
Code>Copy above code and Paste into the white pane.

 

[Guest]

I am getting the following errer: Run-time error '1004':

Unable to set the Visible property of the PivotItem class