Sort Pivot Table based on value of Drop Down List

[dbmagnus]

I’m using Excel2010 and have a PivotTable with 8 columns (7 measures). I want to sort the entire pivot table based the value selected from a drop-down box (data validation). In other words, a user selects a value from the drop down box, and that Pivot Table is sorted on that value (descending). I’m not too savvy with VB code, but I’m pretty sure I don’t want a worksheet change event, as I want to be able to do other things on this sheet without it sorting each time – I’d only like it to sort when the data validation drop down entry changes.

I copied the sorting VB for one column in my pivot:

ActiveSheet.PivotTables("PivotTable2").PivotFields("Game Titles").AutoSort _
xlDescending, "Measure1 ", ActiveSheet.PivotTables("PivotTable2"). _
PivotColumnAxis.PivotLines(4), 1

I'm not sure how to make the value "Measure1" dynamic. I'm sure this is relatively simple, but I am relatively new to VB. Thanks in advance for any help you can provide.