Updating Page Field of Pivot Tables from Form Control

[Guest]

Hi,

I would like to use a pull down menu to feed into a pivot table page field.
So for example, when I select employee name in the pull down menu of the
form, that employee name is used as the page field value for a pivot table.

I'm assuming this needs to be done in VB, but can't work out how. Any advice
would be much appreciated

Thanks,
Ra

 

[Guest]

This is very raw but it works.

Sub PivotTable()
ActiveSheet.PivotTables("PivotTable2").PivotFields("Name").CurrentPage =
ActiveSheet.Range("A8").Value
End Sub

This assumes you are running macro from the activesheet and that the data
and pivot table are on the same sheet. If not please adjust code. It also
assumes that "Name" is the page field title you are using. Lastly it assumes
that the pull down menu value in the form is located in cell A8. Please
adjust as necessary. With that said when this macro runs it will change the
page field Name to whatever is in cell A8. You may want to add this code
along with other code to the change event of the worksheet that contains your
form. That way you can program it so that the pivot table changes/refreshes
automatically whenever the drop down value in the form is changed.

Hope this gets you started.

Thanks,
Bill Horton