+ Reply to Thread
Results 1 to 7 of 7

Excel Office 365 VBA - Pivot table - List of fields and filters locked

  1. #1
    Registered User
    Join Date
    11-16-2019
    Location
    Paris
    MS-Off Ver
    365
    Posts
    5

    Excel Office 365 VBA - Pivot table - List of fields and filters locked

    Hello All,

    I found on a website a VBA code about how to create a pivot table and to have the possibility to choose the function "xlDistinctCount" by adjusting the code to my need.

    When executing the macro, pivot tables are well cretaed but the two first ones have their list of fields and the filters which are locked while for the one that was created last I have access to them. I made a test by deleting the two last pivot tables and I kept only the first one and the result is that finally I have access to the list of fields and the filters. Why I have access to the list of fields and the filters of the pivot table only when there is no other pivot table which follows ?

    Below my code and I attached a file (you just have to execute the macro to get the final result):

    Thank you.

    Please Login or Register  to view this content.
    Attached Files Attached Files

  2. #2
    Registered User
    Join Date
    11-16-2019
    Location
    Paris
    MS-Off Ver
    365
    Posts
    5

    Re: Excel Office 365 VBA - Pivot table - List of fields and filters locked

    Hello All,

    Anyone can help me ?

    Thank you.

  3. #3
    Forum Expert rorya's Avatar
    Join Date
    08-13-2008
    Location
    East Sussex, UK
    MS-Off Ver
    2016 primarily
    Posts
    7,141

    Re: Excel Office 365 VBA - Pivot table - List of fields and filters locked

    I'd imagine it's because your code deletes any existing connections before creating each pivot table. I suggest you remove all but the first instance of this code:

    Please Login or Register  to view this content.
    Rory

  4. #4
    Registered User
    Join Date
    11-16-2019
    Location
    Paris
    MS-Off Ver
    365
    Posts
    5

    Re: Excel Office 365 VBA - Pivot table - List of fields and filters locked

    Hi Rory,

    I did it but I have an error message when executing the macro. Also it seems that for pivot tables which were created, the results in tab "TCD RUPTURE RATE" seem to be wrong as we should have according to the data source in the tab "NDR" :

    - OOS (EUR) = 792,58 but instead we have 8 870
    - OOS (CON) = 504 but instead we have 8 873
    - Number of SKUs = only this datafield is good as we have 1

    Also, is there any way to set several "objconnection" (for each pivot table) like set objconnection 1, set objconnection 2 ... ?

    Please find attached the file in which I remove the first instance of the code and you will see the result.
    Attached Files Attached Files

  5. #5
    Forum Expert rorya's Avatar
    Join Date
    08-13-2008
    Location
    East Sussex, UK
    MS-Off Ver
    2016 primarily
    Posts
    7,141

    Re: Excel Office 365 VBA - Pivot table - List of fields and filters locked

    I didn't say to remove all of them. The first one should be left in.

    I only have 2010 here so I can't test that code.

  6. #6
    Registered User
    Join Date
    11-16-2019
    Location
    Paris
    MS-Off Ver
    365
    Posts
    5

    Re: Excel Office 365 VBA - Pivot table - List of fields and filters locked

    Sorry,

    I just removed the first one in this the new attached file. I have now the good results for the pivot table in the tab "TCD RUPTURE RATE" but unfortunately the list of fileds and the filters are still blocked for all pivot table except the last one in tab "TCD VALUE AND VOLUME OP REGIE".

    Thank you.
    Attached Files Attached Files

  7. #7
    Registered User
    Join Date
    11-16-2019
    Location
    Paris
    MS-Off Ver
    365
    Posts
    5

    Re: Excel Office 365 VBA - Pivot table - List of fields and filters locked

    Hello All,

    Any idea ?

    Thank you.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Replies: 1
    Last Post: 07-16-2015, 05:46 AM
  2. [SOLVED] Office 2013 Pivot Table Filters Going Weird
    By xz_LeStat_zx in forum Excel General
    Replies: 13
    Last Post: 04-11-2015, 03:29 PM
  3. Replies: 4
    Last Post: 06-19-2014, 12:59 PM
  4. Change multiple pivot table filters with dropdown list in cell
    By rlsublime in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 08-28-2013, 09:19 AM
  5. [SOLVED] Getting a list of all possible combinations from different fields in a Pivot Table
    By wetbean in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 05-19-2013, 04:40 PM
  6. Pivot Table Drop Down List (filters)
    By ndreid in forum Excel General
    Replies: 2
    Last Post: 08-22-2012, 01:52 PM
  7. Collapse/Expand - Pivot table Fields - Need equivalent option in Excel VBA Pivot table
    By ragavendraph in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 06-07-2012, 03:00 PM

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