Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
VBA Code with Pivot tables
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...! |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
VBA Code with Pivot tables
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 wrote in message ups.com... 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...! |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
VBA Code with Pivot tables
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. -- Regards Roger Govier wrote in message ups.com... 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...! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Average Calculations from Pivot Tables - Get Pivot Data? Calc Fiel | Excel Worksheet Functions | |||
build a pivot table from multiple other pivot tables. | Excel Discussion (Misc queries) | |||
Pivot Tables -changing datasource for exsting Pivot Table | Setting up and Configuration of Excel | |||
how do I consolidate multiple pivot tables into one pivot table? | Excel Discussion (Misc queries) | |||
How does the term 'pivot' apply to Excel's Pivot tables and Pivot. | Excel Discussion (Misc queries) |