Hey Tom

The macro stopped at pvtItm.Visible = True

"Tom Hutchins" wrote:

Try the following macro. Paste it into a VBA module in your workbook. This
macro assumes your pivot table is named "PivotTable1". Click any cell on the
sheet where the pivot table is, then run the ShowItem macro (Tools Macro
Macros ShowItem RUn).

Option Explicit

Public Sub ShowItem()
'Declare local variables
Dim ItemFound As Boolean, x As Long, pvtItm
Const WhichFld = "Rel"
Const SelItem = "4.05.50"
ItemFound = False
'Make the first pivotitem visible
Set pvtItm = ActiveSheet. _
PivotTables("PivotTable1"). _
PivotFields(WhichFld$).PivotItems(1)
pvtItm.Visible = True
'Hide every item in the pivottable that does not
'match SelItem.
For x& = 2 To ActiveSheet. _
PivotTables("PivotTable1"). _
PivotFields(WhichFld$).PivotItems.Count
Set pvtItm = ActiveSheet. _
PivotTables("PivotTable1"). _
PivotFields(WhichFld$).PivotItems(x&)
If Len(pvtItm) = Len(SelItem) Then
If Left(pvtItm, Len(SelItem)) = SelItem Then
pvtItm.Visible = True
ItemFound = True
Else
pvtItm.Visible = False
End If
Else
pvtItm.Visible = False
End If
Next x&
'Unless the first PivotItem matches SelItem, hide it.
Set pvtItm = ActiveSheet. _
PivotTables("PivotTable1"). _
PivotFields(WhichFld$).PivotItems(1)
If Len(pvtItm) = Len(SelItem) Then
If Left(pvtItm, Len(SelItem)) = SelItem Then
ItemFound = True
Else
pvtItm.Visible = False
End If
Else
pvtItm.Visible = False
End If
'If no item in the pivottable matches SelItem,
'display an error message and quit.
If ItemFound = False Then
MsgBox SelItem & " not found in pivot table"
Exit Sub
End If
'Free object variables
Set pvtItm = Nothing
Exit Sub
SIerr:
MsgBox Err.Description
End Sub

Hope this helps,

Hutch

"Beep Beep" wrote:

While in the pivot table and looking at the Fields I have a field called Rel.
This contains anywhere from 50 to 100 different releases such as: 4.0 - 4.5
- 4.05.50 etc. I know while in the drop down box where it ask you to show all
or select the ones you want I can just start the number and it will
automatically go to it. However these are not sorted (not my data) and I have
to manuualy search for the ones that are like 4.05.50. I would like a macro
to run inside this drop box and put a check mark against the string of
releases. Can do?? Any other suggestions.