VBA Code with Pivot tables

J

jeff.white

Need help/suggestions on my little problem. I have a basic pivot
table that list a job code number, say 500 in the list along with
their count as tallied via the piv. In column H, outside the piv, I
have a list of 'hot' job codes, there are are about 60 of these. What
I would like to do is have VBA only show, 'TRUE' of those items listed
in column H. And those that don't match to be set to FALSE.

Any ideas out there?

Thanks...!
 
B

Bernie Deitrick

Jeff,

Dim myPI As PivotItem

For Each myPI In ActiveSheet.PivotTables("PivotTable1").PivotFields("Field Name").PivotItems
If IsError(Application.Match(CDbl(myPI.Name), ActiveSheet.Range("H:H"), False)) Then
myPI.Visible = False
Else
myPI.Visible = True
End If
Next myPI


The CDbl is needed in case your code numbers are numbers: you may need to play with that part of the
code. And, of course, you will need to change "PivotTable1" and "Field Name" to actual values.
You can get those by recording a macro....

HTH,
Bernie
MS Excel MVP
 
R

Roger Govier

Hi Jeff

Why not put an extra column in your source data titled Hot.
In the column enter
=IF(ISNA(MATCH(D2,$G$1:$G$60,0)),"","Hot")

This formula assumes your Job code is in D, and your list of "hot" codes
is in G1 to G60.
Amend as necessary.
Drag the Hot field to the Page area of the PT, and filter for Hot.
 
Top