+ Reply to Thread
Results 1 to 7 of 7

Use a Variable name for a Pivot Table to reformat pivots

  1. #1
    Forum Contributor
    Join Date
    08-29-2013
    Location
    Melbourne, Australia
    MS-Off Ver
    Excel 2003/7/10/13
    Posts
    860

    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
    Last edited by AliGW; 01-03-2018 at 04:21 AM.
    Remember you are unique, like everyone else

  2. #2
    Forum Contributor
    Join Date
    08-29-2013
    Location
    Melbourne, Australia
    MS-Off Ver
    Excel 2003/7/10/13
    Posts
    860

    Re: Re Variable name for a Pivit Table to reformat pivots

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

  3. #3
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    Excel 2013 (Win 10 - Work) & 365 Subscription (Win 10 - Home)
    Posts
    17,870

    Re: Re Variable name for a Pivit Table to reformat pivots

    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.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    If anyone has helped you towards solving a problem, then you can thank them by clicking on their reputation star bottom left.

  4. #4
    Forum Contributor
    Join Date
    08-29-2013
    Location
    Melbourne, Australia
    MS-Off Ver
    Excel 2003/7/10/13
    Posts
    860

    Re: Re Variable name for a Pivit Table to reformat pivots

    Yes, apologies for that. Thank you.

  5. #5
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    Excel 2013 (Win 10 - Work) & 365 Subscription (Win 10 - Home)
    Posts
    17,870

    Re: Re Variable name for a Pivot Table to reformat pivots

    No probs.

  6. #6
    Forum Contributor
    Join Date
    08-29-2013
    Location
    Melbourne, Australia
    MS-Off Ver
    Excel 2003/7/10/13
    Posts
    860

    Re: Re Variable name for a Pivot Table to reformat pivots

    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
    Attached Files Attached Files

  7. #7
    Forum Contributor
    Join Date
    08-29-2013
    Location
    Melbourne, Australia
    MS-Off Ver
    Excel 2003/7/10/13
    Posts
    860

    Re: Use a Variable name for a Pivot Table to reformat pivots

    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.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1