Stopping new fields from being auto checked in pivot table field dropdown

[leocash88]

I love this board, thanks in advance for anyone who can help me with this problem I'm having.

I have this pivot table that I deploy to the sales team every week, keeping track of all our backorder in the system.

On one of the field 'item number' in the pivot table , I have pre-selected some items from this field drop down list so the sales only need to pay attention to those items. For example, if we have 100 items, I would have unchecked all of them, and just checked 25 of those items so that 75 items will be hidden.

Problem arises when the source data has some new items added. For example a few rows of new items were added, the pivot table now pivots 102 items. When the sales refresh the table, 27 items from the field dropdown list (as oppsed to 25 originally) are checked because excel will automatically check and display any new addition to the field.

How can I prevent this from happening, somehow get excel to NOT have new items checked by default. So that the pivot table will only have the 25 items checked like how I intended to.

I am using Excel 2003.

Thanks for any help, much appreciated.
Leo

 

[leocash88]

An alternative to my own question.

Can you guys please help me with the code to lookup a column i.e. datapage!A1:A25, then use that column's data to select the 25 items in the pivot table's dropdown field list? (located in pivottable!A3), possible?

Thanks
Leo

 

[leocash88]

OK, I know it is possible now.. I just test did a macro recording

With ActiveSheet.PivotTables("PivotTable1").PivotFields("ITEM")
.PivotItems("ABC-3818").Visible = True
.PivotItems("ABC-3819").Visible = True
.PivotItems("ABC-3823").Visible = True
.PivotItems("ABC-3825").Visible = True
.PivotItems("ABC-4002").Visible = True
.PivotItems("ABC-6312").Visible = True
.PivotItems("ABC-6315").Visible = True
.PivotItems("ABC-6318").Visible = True
.PivotItems("ABC-6321").Visible = True
.PivotItems("ABC-6322").Visible = True
End With
End Sub

It's now just a matter of getting the code to look through a column and select these ABC-3138 through ABC-6322 items. (Please help! It's going to be in the 'datapage' tab, starting cell A1 to cell A25 or what so ever.)

How about error handling? If my lookup table have ABC-3123 but it's not in the PivotTables("PivotTable1").PivotFields("ITEM") list?

I'll post on this thread when I have figure out the code to help others who might have the same problem as I do.