Use a Variable name for a Pivot Table to reformat pivots

Hi Forum,

I use Pivots all the time & I like to have them display as Classic layout with no sub Totals.

Because Excel gives each Pivot Table a number, this means I need to manually go through & change the formatting when I'd rather write a VBA macro that could loop through all pivots and make these changes.

Has anyone worked out a way to do these steps using a variable name for the pivot table and a method make all Field Settings to 'None'.

Thanks

Hi Forum, I've moved this question to the VBA Macros section as it may be more suitable. Thanks

You are not allowed to open duplicate threads, nor are you able to move your own threads. In future, contact an admin or mod for this sort of thing, please. I have moved this thread for you and marked it unsolved. Thanks.

Yes, apologies for that. Thank you.

No probs.

Hi Forum,

I've made some progress on this problem but the last part is throwing an error.

I can get the Pivot to Display in Classic mode but when I try to remove the Subtotals, I'm getting the below error;

Runtime Error # 438 'Object doesn't support the property or method'

This is my code & the file is attached;

Please Login or Register  to view this content.

Does anyone have any suggestions?

Thanks

Hi Again,

I've got the solution & am posting it here in case anyone else wants to have any Pivot Tables changed to the display type 'Classical' & then all Subtotals removed.

This is a mix of parts from the 'net & some adaptation of recorded macros.

It's not the tidiest code but works.

Enjoy!

Please Login or Register  to view this content.