Removing Column, Row and PageFields in Pivot Table

[Wilfried_1983]

Hey guys, I have the following problem.

I have a Pivot Table that is linked to an Access Database.
In order to get the required results for my management report I need to remove and drop a lot of column, row and page fields which requires a lot of time.

I have recorded and tweaked over 40 macros to perform these routines. However, the catch is that I manually need to remove all items from the Pivot Table before running a new macro.

Trying to avoid this, I wrote this little piece of code.
The part of removing the datafields on its own works, however the part of removing the column, row and pagefields doesn´t.
I found that there are the following classes in the VBA library: xlColumnField, xlPageField, xlRowField. Should I incorporate these into the code, if so, how?

This is the code I have written so far:

Sub TestClearAll()
Sheets("PT").Select
Dim PT As PivotTable, ptField As PivotField, ptColumn As PivotField, ptRowField As PivotField, ptPageField As PivotField
Set PT = ActiveSheet.PivotTables("PT")
For Each ptField In PT.DataFields
    ptField.Orientation = xlHidden
Next ptField
For Each ptColumn In PT.ColumnFields
    PT.ColumnFields.Orientation = xlHidden
Next ptColumn
For Each ptRowField In PT.RowFields
    PT.RowFields.Orientation = xlHidden
Next ptRowField
For Each ptPageField In PT.PageFields
    PT.PageFields.Orientation = xlHidden
Next ptPageField
Set PT = Nothing

End Sub

Thanks in advance

 

[Fazza]

Hi, Wilfried.

Sounds like a query table would be better than a pivot table in this instance.

Via menu data, import external data, etc. OK?

regards, Fazza

 

[Wilfried_1983]

Hi Fazza,

I guess my "Pivot Table" is already a "Query Table" then. I created it exactly with the method you described, import external data etc.

However, my question is how to remove all column, row, page and data fields that my macro dropped in the table before. I do not want to refer to the exact fields because they change with every macro. I want to write a routine that loops through the fields and removes them.

I hope I made myself clear.

 

[Fazza]

I do not understand. Any chance of an image of the desired result?