I am not an experienced VBA guy. Mostly record and look with little tweaks. I
have a pivot table that provides data for a list of customers. I have text
boxes for each customer with assigned macros to filter on the customer name.
The reason for the buttoins vs. normal pivot table functions is that this
table is designed for inexperienced users who would otherwise not use the
information. This all works but is a little cumbersome. What I would like is
one button that pulls desired customer information form a CELL which could be
a dropdown list. This would also would make it much easier to add or subtract
customers because I would only have to adjust my list vs creating a button
and macro for each individual customer. Here is what each buttton performs:

Sub Cust_MinnPwr()
'
' Cust_MinnPwr

Sheets("By Train Summary").Visible = True
Sheets("By Train Summary").Select
ActiveSheet.PivotTables("Main Display").PivotFields("Customer"). _
ClearAllFilters
ActiveSheet.PivotTables("Main Display").PivotFields("Customer"). _
PivotFilters.Add Type:=xlCaptionEquals, Value1:="Minn Pwr"
Range("i17").Select
End Sub


Any assistance would be much appreciated.

Take one of the existing macro's, rename it if you wish, and do the following
Create a variable called eg varCust
Insert a line with the following code:
varCust = InputBox("For which customer do you wish to view data?", _
"Enter customer name")
Now change your existing line
ActiveSheet.PivotTables("Main Display").PivotFields("Customer"). _
PivotFilters.Add Type:=xlCaptionEquals, Value1:="Minn Pwr" to read
ActiveSheet.PivotTables("Main Display").PivotFields("Customer"). _
PivotFilters.Add Type:=xlCaptionEquals, Value1:=varCust

--
Hth

Kassie Kasselman
Change xxx to hotmail


"looper" wrote:

I am not an experienced VBA guy. Mostly record and look with little tweaks. I
have a pivot table that provides data for a list of customers. I have text
boxes for each customer with assigned macros to filter on the customer name.
The reason for the buttoins vs. normal pivot table functions is that this
table is designed for inexperienced users who would otherwise not use the
information. This all works but is a little cumbersome. What I would like is
one button that pulls desired customer information form a CELL which could be
a dropdown list. This would also would make it much easier to add or subtract
customers because I would only have to adjust my list vs creating a button
and macro for each individual customer. Here is what each buttton performs:

Sub Cust_MinnPwr()
'
' Cust_MinnPwr

Sheets("By Train Summary").Visible = True
Sheets("By Train Summary").Select
ActiveSheet.PivotTables("Main Display").PivotFields("Customer"). _
ClearAllFilters
ActiveSheet.PivotTables("Main Display").PivotFields("Customer"). _
PivotFilters.Add Type:=xlCaptionEquals, Value1:="Minn Pwr"
Range("i17").Select
End Sub


Any assistance would be much appreciated.