Removing Column, Row and PageFields in Pivot Table

Wilfried_1983

New Member
Joined
Jul 17, 2008
Messages
15
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. :rolleyes:
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:

Code:
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
 

Some videos you may like

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.

Fazza

MrExcel MVP
Joined
May 17, 2006
Messages
9,368
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

New Member
Joined
Jul 17, 2008
Messages
15
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

MrExcel MVP
Joined
May 17, 2006
Messages
9,368
I do not understand. Any chance of an image of the desired result?
 

Watch MrExcel Video

Forum statistics

Threads
1,122,710
Messages
5,597,702
Members
414,164
Latest member
ARTW

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top