Pivot tables - can change row/col vars but can't get rid of existing vars

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top