Sub-totals in Pivot tables RRS feed

  • Question

  • Is there a way to allow someone use subtotals on a pivot table that is protected

    currently my code to protect the sheet has

    ActiveSheet.Protect "XXX", DrawingObjects:=True, Contents:=True, Scenarios:=True _
            , AllowFiltering:=True, AllowUsingPivotTables:=True, AllowFormattingColumns:=True

    is there an extra critrea I can use to enable subtotals?



    Wednesday, March 25, 2015 7:41 PM


  • Hi David,

    Based on my understanding, the Excel object is used to automate Excel application. Only the operation which can be done in manually that could be using code to automate.

    And as far as I test, event I check all the options when I protected the worksheet, the subtotal still can't be used. As a workaround, I suggest that you unprotect the worksheet before you subtotal in the pivot table and protect it back.

    Also if you do want Excel application to provide this feature in the further version, I suggest that you submit the feedback from 'Smile face' at the top right corner if you are using Exel 2013.

    If not, you can submit the feedback from link below:
    Submit Feedback - Microsoft Support

    Regards & Fei

    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Friday, March 27, 2015 2:17 AM