Changing DataFields in Data Model using VBA

[IrishChristof]

Hi,

I'm trying to do a VBA script that will allow me to change what is shown on a pivot table I have build from my Data Model.
So when I change the value in cell AO5, it will change from displaying Sum of Pax (sorted largest to smallest), to Sum of Total Revenue (largest to smallest).

The script worked on a normal Pivot, but doesnt seem to work on a pivot from the Data Model.

Private Sub CommandButton1_Click()




        Dim pt1 As PivotTable
        Dim Field1 As PivotField
        Dim pi As PivotItem
        Dim sumField As String


        sumField = Range("AO5").Value


        '-----Pivot1-----




        Set pt1 = PivotTables("PivotTable1")




        pt1.DataFields(1).Orientation = xlHidden
        pt1.PivotFields(sumField).Orientation = xlDataField
        pt1.AllowMultipleFilters = True
        pt1.RefreshTable


        If sumField = "Total Revenue" Then


        ActiveSheet.PivotTables("PivotTable1").PivotFields("Main Tour Code").AutoSort _
        xlDescending, "Sum of Total Revenue", ActiveSheet.PivotTables("PivotTable1"). _
        PivotColumnAxis.PivotLines(1), 1


        Else




        ActiveSheet.PivotTables("PivotTable1").PivotFields("Main Tour Code").AutoSort _
        xlDescending, "Sum of Pax", ActiveSheet.PivotTables("PivotTable1"). _
        PivotColumnAxis.PivotLines(1), 1


        End If

End Sub