Check/unCheck PivotItems from another sheets column

**Final Impact** · 11-16-2009, 03:44 PM

Hello everyone I’m new to the forum and need some help. Let me open with the fact that I have limited skills in the programming department so thanks in advance for your support.

I have three sheets. One is data, one is a pivot table and one is the filter data which will have serial numbers I want to see in my pivot table “data, output, & filter”. My goal is to use a column of cells in “filter” sheet to hide and un-hide pivot items in the first column of “output”. The column is SERIAL_NUM.

The PT comes from a table of test records with 70 columns and 15k rows ['CAL.DBF(2009-11-04)'!$A$1:$BS$15000]. My PT is setup but I have to manually uncheck "Show All" and search for and check-off 200 serial numbers in column 1 of the PT [SERIAL_NUM]. Can I execute a macro which allows me to select from a list or range cells I specify (like A2 to A201 or A2:A62)?
I need to uncheck everything, and then check 'PivotItems Visible = True' to make items from my list visible. This would hide 14,000 plus records in the column. I only need to do this for one PT field “SERIAL_NUM” and manually check/uncheck the other field items as needed.

It looks like I need a conditional statement and a loop but I’m not sure how to do this.

Does this make sense? Thanks for any input!
Final Impact

**Final Impact** · 11-17-2009, 12:27 PM

Question: Do I need to count the records in the table to toggle each one from : PivotItems("16679").Visible = True (to False?) OR is there a global means of unchecking pivotitems for 15K records? Recording a macro doesn't help. If I need to count them, it looks like the pivot cache would give the answer.

I saw this user defined function which is helpful but I'm not sure how to convert it from "Function" to something useful. Any hints?

Please Login or Register  to view this content.

With the above infomation I could to a For statement to toggle .Visible = false

Any hints?

Thanks
FI

**Andy Pope** · 11-17-2009, 12:33 PM

You should be able to loop through the pivotfields items, although if you have 15K unique items it may have problems.

Can you post a small sample file, say 4 columns, 100 records and 3 filter records. Set the PT up with the required filtering manually applied.

**Final Impact** · 11-17-2009, 02:36 PM

Andy,
Here is the raw data, two pt's and the list.

Cal.dbf is the data.
Slot By Month is the first cut of SERIAL_NUM to use in the next PT filter.
SN Retest is the second PT where the Serial_Num is from the above step is entered into the filter after Select none is applied.

I know I can base one PT on another but I must to do this manually and keep them seperate as shown here.

Problems I can anticipate:
refresh issues, errors for missing records, and things of that nature.

Thanks for looking.
Any help is appreciated!
Final Impact

ALL - the file is clean and free of know viruses. It was safe when it left.

**Andy Pope** · 11-18-2009, 06:55 AM

With code, which is very slow.
I named the range of Filter ID's, H5:H21, as FAILED

Please Login or Register  to view this content.

A quicker approach is to add a field to your pivot chart data source and use that as the page field.

I added a new field, called SERIAL_FILTER, with the following formula.
=MATCH(A2,FAILED,0)>0

Update the data source to include the new field and then used as the Page field showing TRUE.