Quick Pivot Table Question...

[danomaniac]

Greetings Everyone!
I've got a pivot table that shows the open Purchase Orders and Order Quantities of about 800 different Part numbers. I want it to just display the part number the user picks, but initially all part numbers are visible. Short of having to manually uncheck everyone, I'd like to do it through VBA (or if there's a wildcard I'm missing somewhere).

I'm trying something like this, and I know it's not right, but I'm not sure what I'm missing...

For Each PivotItem In ActiveSheet.PivotTables("PivotTable1&quot.PivotFields("Material&quot
.Visible = False
Next


Thanks for any help.

 

[acron]

Try using

For Each PivotItem In ActiveSheet.PivotTables("PivotTable1&quot.PivotFields("Material&quot.PivotItems

It is a nuisance that you must select each item individually, and drove me to putting together a utility to overcome this limitation. It is based around a userform and has somewhat extensive code and so cannot be posted here. It allows Select All or block selection using Shift or Ctrl keys. If you would like a copy let me know where I can send it. It is a bit rough and ready but seems to work ok.

A.C.

 

[SkipVought]

Hi danomanic,

This is not a very elegant solution, but it uses the AutoFilter functionality, which you, no doubt, have discovered does NOT work IN a PivotTable.

BUT...
it does work OUTSIDE of a PivotTable. So...

1. Anchor the PivotTable to C1, for instance.

2. Write some code to propogate the row values in Col C into Col A whenver the PivotTable is refreshed.

3. Hide Cols B & C

4. Filter on Col A -- VOLA! Skip,
metzgsk@voughtaircraft.com

 

[danomaniac]

Hey Skip,
Excellent suggestion. I've been playing with that this morning and I've come up with 4 separate pivot tables that contain different types of info relating to a specific part number: Basically, one lists open jobs, one lists open p.o.'s, one lists sub-assemblies and sub partnumbers/quantities, etc, and one lists forecasted requirements. It's too much to put on one table because it becomes difficult to read. Here's where your suggestion comes in: When I select a p/n in the first table, I want to update the other three with the same p/n. I've got the code written to update the tables, but I can't figure out how to make the code execute when the first table is refreshed.

Thanks,

Dan

 

[SkipVought]

danomaniac,
There is not Worksheet Event associated with the AutoFilter dorpdown selection. What you would have to do is add a ComboBox where the AutoFilter DropDown would be, populated with the PivotTable Row values. Then you can control what happens in the ComboBox_Click Event.

With ActiveCell       'the cell where the dropdown is
   .ComboBox1.Top = .TopLeftCell.Top
   .ComboBox1.Left = .TopLeftCell.Left
   .ComboBox1.Width = .TopLeftCell.Width
   .ComboBox1.Height = .TopLeftCell.Height
   .ComboBox1.Visible = True
End With

This will fit it right into the cell. You could make the ComboBox Visible when that cell is activated and False when the Click Event is complete.

Hope this helps. Skip,
metzgsk@voughtaircraft.com

 

[danomaniac]

Thanks Skip,
I'll keep after it. I was kind of thinking I'd have to add my own combo box.