A
Andreww
Hi - I am trying to create an xl report based on pivot tables where
the user selects variables from a drop down control and these are
picked up by mode vba code and changes the pivot.
My problem is that while the code essentially works, the previously
chosen vars remain in the pivot!
The code adds vars to the pivot rather than replacing them!
I use the following code that can change the vars in a pivot table.
Sub AllWorkbookPivots()
Dim pt As PivotTable
Dim ws As Worksheet
Dim ptcache As PivotCache
Dim mrow, mcol As Variant
mrow = Sheets("Admin").Range("F3").Value
mcol = Sheets("Admin").Range("F5").Value
Set pt = Sheets("Sheet1").PivotTables(1)
With Sheets("Sheet1").PivotTables(1)
.PivotFields(mrow).Orientation = xlRowField
.PivotFields(mcol).Orientation = xlColumnField
.PivotFields("count").Orientation = xlDataField
End With
End Sub
Any ideas?
Thanks
Andrew
the user selects variables from a drop down control and these are
picked up by mode vba code and changes the pivot.
My problem is that while the code essentially works, the previously
chosen vars remain in the pivot!
The code adds vars to the pivot rather than replacing them!
I use the following code that can change the vars in a pivot table.
Sub AllWorkbookPivots()
Dim pt As PivotTable
Dim ws As Worksheet
Dim ptcache As PivotCache
Dim mrow, mcol As Variant
mrow = Sheets("Admin").Range("F3").Value
mcol = Sheets("Admin").Range("F5").Value
Set pt = Sheets("Sheet1").PivotTables(1)
With Sheets("Sheet1").PivotTables(1)
.PivotFields(mrow).Orientation = xlRowField
.PivotFields(mcol).Orientation = xlColumnField
.PivotFields("count").Orientation = xlDataField
End With
End Sub
Any ideas?
Thanks
Andrew