Change data fields for pivot table

I have a workbook where I am using a number of Sumproduct calculations to tabulate different metrics (20) for operations.  Each metric is in it's own column with a field name.  The sheet allow selection of a client from a data validation box and then summarizes for the previous twelve months by each type of account.   The calculation speed is very slow because of all the formulas.  I would like to change these summaries to pivot tables.  

My idea is to create a pivot table with the client for pages, months for column headings, account type for row headings, and each metric as the datafield.  I would like to use VBA to change the datafield to the new metric using a drop down list for selection.   After the selection code would change the datafield and refresh the table.  

I am relatively new VBA and can't quite figure this out.  I tried the macro recorder for this and it generated the following code which I tried to modify.  

    Range("A7").Select
    ActiveSheet.PivotTables("PivotTable5").PivotFields("Sum of Placements"). _
        Orientation = xlHidden
    ActiveSheet.PivotTables("PivotTable5").AddDataField ActiveSheet.PivotTables( _
        "PivotTable5").PivotFields("Payments"), "Sum Of Payments", xlSum

Currently I am testing this with command buttons and get an error when the same button is pressed twice concurrently.  Perhaps I need to remove a datafield before I add one, and not just hide it, or use some error trapping.  I would also like to remove all of the filters that had been applied in the previous step so all data would be displayed.   I would list all of the data fields in the list box in $B$4 and need some code to link the data field selection to the list box.

Which event would contain the code so when the metric is chosen, the process would run.  

The pivot table starts in $A$7

This process would allow one pivot sheet to view each of the metrics with a selection from the list box.  Please let me know if you need additional clarification.

Thanks in advance for your help.
TerryStart Free Trial