Excel Pivot Table Drop Down Macro

[richard.karesh]

I am running a process which downloads information for ~500 people.
This information is then placed into a pivot table and distributed to a
number of managers. Each manager has 10 to 20 people working for
him/her. Currently, a manager must manually select the individuals
from the Names drop down to examine their information. Is there a
macro (which I can provide the managers) which will take a list of
Names, and go to the filed of Names in the pivot table and select only
those names from the drop down list?

 

[Guest]

Richard

Here are 2 short routines that might help. They allow you to expand and
collapse any field in a pivot table. Collapse will leave only the first item
in a field visible. This way the manager only needs to turn off one persons
data and then check on the few they want to see.

Give your managers 2 buttons on the sheet that conains the pivot. One to
run EXPAND and the other to run COLLAPSE. Then they click the button, type
in the name of the field they want to expand or collapse and that is it.

Mike
Sub TBLcollapse()
Dim PT As PivotTable
Dim STRG1$, itemcnt%

Set PT = ActiveSheet.PivotTables(1)
STRG1 = InputBox("Enter the name of the field you want to collapse",
"COLLAPSE FIELD")
itemcnt = PT.PivotFields(STRG1).PivotItems.Count

For i = 2 To itemcnt
With PT.PivotFields(STRG1)
.PivotItems(i).Visible = False
End With
Next i
Set PT = Nothing
End Sub

Sub TBLexpand()
Dim PT As PivotTable
Dim STRG1$, itemcnt%

Set PT = ActiveSheet.PivotTables(1)
STRG1 = InputBox("Enter the name of the field you want to expand",
"EXPAND FIELD")
itemcnt = PT.PivotFields(STRG1).PivotItems.Count

For i = 1 To itemcnt
With PT.PivotFields(STRG1)
.PivotItems(i).Visible = True
End With
Next i
Set PT = Nothing
End Sub

 

[richard.karesh]

Thanks - I forgot to mention, I publish an updated report 2 times a
week. Right now, the table is going against ~50,000 records of which I
need to make sophisticated calculations on before loading into a pivot
table. When it grows to 65k - it will be loaded into access in
discrete chunks.