Tracing GetPivotData Calls to Pivot Tables and Work Sheets

[Jabba1963]

Hi All,


Sadly got myself in a bit of a mess... I am creating a dashboard and in the process I have created numerous sheets calling a finite number of Pivot Tables. The Pivot Tables are very large and I think that in my excitement I have created at least a couple of Pivot Tables that are no longer referenced so hence for performance/memory reasons I need to remove them. The problem is that I am not sure which ones they are ?!!


Is there an easy way of tracing calls to GetPivotData function within a workbook and ascertaining the name of the PivotTables referenced on each and every worksheet ?


Initially I would like to loop through the workbook and report references to Pivot Tables for each Sheet and then perhaps thereafter list the cell references making the calls.


Also as a by-the-by - is there any truth in what I read somewhere, that copying a PivotTable and then changing its attributes uses less resources than simply creating a new PivotTable from scratch each time ? Something to do with shared cache perhaps ?


I am relatively new to VBA and loving it...


Any assistance gratefully received

 

[dan_l]

How many pivot tables are we talking about?


The second argument to getpivotdata should be a cell specific to the pivot table. I think without any augmentation by you, it's the upper left cell in the table. A little vba should help:


you can extract the formula from the cell with range.formula. Treat that as a string to get that 'home cell'. Look up the home cells and see which ones aren't being used.

 

[Jabba1963]

Thanks for posting a reply - and my apologies but I got sidetracked and hence the late response to your reply.


And yes - much as I envisaged thanks.