Sometimes a long line of code or a long comment wraps (splits into 2 lines)
when posted on the forum. That causes an error when the cose is posted into
the VBA editor. Here is a slightly revised version which should avoid this
problem (I have also added comments to the code):

Public Sub ShowItem()
'Declare local varaiables
Dim SelItem As String
Dim ItemFound As Boolean, pvtItm
'Get the text entered by the user.
SelItem = ActiveSheet.Range("A1").Value
ItemFound = False
'Make every item in the pivottable visible.
For Each pvtItm In ActiveSheet. _
PivotTables("PivotTable1"). _
PivotFields("Product").PivotItems
pvtItm.Visible = True
'If a pivottable item matches the user's text, set
'ItemFound to TRUE (was previously FALSE).
If pvtItm.Value = SelItem Then
ItemFound = True
End If
Next pvtItm
'If no item in the pivottable matches the user's text,
'display an error message and quit.
If ItemFound = False Then
MsgBox SelItem & " not found in pivot table"
Exit Sub
End If
'Hide every item in the pivottable that does not
'match the user's text.
For Each pvtItm In ActiveSheet. _
PivotTables("PivotTable1"). _
PivotFields("Product").PivotItems
If pvtItm.Value = SelItem Then
pvtItm.Visible = True
Else
pvtItm.Visible = False
End If
Next pvtItm
End Sub

To use this macro:
- right-click on any sheet tab in your workbook to open the VBA editor
- From the menu bar, select Insert Module
- Copy & paste the code above into the module
- From the menu bar, select Debug Compile VBAProject
- Save the workbook
- You might add a command button on your worksheet near the user input
cell. Right-click on the button and select 'View code'. In the Click event
subroutine that appears, add the code 'Call ShowItem', as follows:

Private Sub CommandButton1_Click()
Call ShowItem
End Sub

Save, close, and re-open your workbook. The button should work.

The macro above assumes the pivvottable is named PivotTable1. To see the
name of your pivottable, click any cell in the pivottable. Then display the
Pivot Table toolbar and select Pivot Table Table Options. The name of your
table is displayed and can be changed in the box that appears.

Likewise, the macro assumes the pivottable field you are trying to match is
called 'Product'. Change it to the correct field name.

Hope this helps,

Hutch

"Becky" wrote:

Ok, I'm not that adept at using the editor. Can you explain more in detail
what I need to do, especially when you said "unwrap" below?

"Tom Hutchins" wrote:

This macro may provide a starting point for you:

Sub ShowItem()
Dim SelItem As String, ItemFound As Boolean, pvtItm
SelItem = ActiveSheet.Range("A1").Value
ItemFound = False
For Each pvtItm In
ActiveSheet.PivotTables("PivotTable1").PivotFields ("Product").PivotItems
pvtItm.Visible = True
If pvtItm.Value = SelItem Then ItemFound = True
Next pvtItm
If ItemFound = False Then
MsgBox SelItem & " not found in pivot table"
Exit Sub
End If
For Each pvtItm In
ActiveSheet.PivotTables("PivotTable1").PivotFields ("Product").PivotItems
If pvtItm.Value = SelItem Then
pvtItm.Visible = True
Else
pvtItm.Visible = False
End If
Next pvtItm
End Sub

The 2 "For Each pvtItm" statements will have to be 'unwrapped' when you
paste them into the VBA editor in Excel.

Hope this helps,

Hutch

"Becky" wrote:

Here's the situation. It's hard to imagine without seeing the whole sheet,
but here goes.

Assume someone enters a product number (e.g., 1438) into cell A1. Let's say
I have a pivot table about 30 rows down that lists all the sales data YTD
(with many different product numbers). I also have "ship to locations" for
where this product is delivered and I want this displayed as well (along with
respective VOL, $, etc.)

I want to have a macro (or something equivalent) that will filter on the
pivot data, so that only the product number listed in cell A1 is displayed in
the pivot table below (with all the possible ship-to locations &
corresponding data as well). It's the equivalent of clicking on the pivot
drop-down menu and selecting the one product number.

However, sales will change the product number depending on whatever number
they want to input, so I can't have the macro specify a specific number, but
just "any number".

Hope this makes some sense.