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

[Tinytoons]

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.

[Tinytoons]

Hello All,

Anyone can help me ?

Thank you.

[rorya]

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.

[Tinytoons]

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.

[rorya]

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.

[Tinytoons]

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.

[Tinytoons]

Hello All,

Any idea ?

Thank you.