LinkBack URL
About LinkBacks
How can I locate pivot tables that exist in my workbook? Sometimes I create a pivot table, but I don't remember in what cell it was created. It is very difficult to locate it during subsequent use. I can't find it in the list of cell names, either.
How can I locate pivot tables that exist in my workbook? Sometimes I create a pivot table, but I don't remember in what cell it was created. It is very difficult to locate it during subsequent use. I can't find it in the list of cell names, either.
Hi Carlossaltz,
Are you saying you can't see the pivot table outline when the pivot table isn't currently selected? It's clear as day in my Excel 2003 and 2007.
Due to the way pivot tables can over-write columns and rows, I tend to always put them onto their own worksheet in A1. Your choice to put it elsewhere though.
Maybe the PT has not had any row/column/data or page items set.
In which case it would not be easy to see other than by some possible cell formatting.
Once the table area is selected you should see all the blue bordering and text describing the table.
anyway try this code in the immediate window.
ALT+F8 (open VBE)
CTRL+G (immediate window)
If you have any pivot tables their sheet and cell location will be outputted in the immediate window.
for each sht in worksheets:for each pt in sht.pivottables:debug.Print sht.name,pt.tablerange1.address:next pt:Next sht
I didn't express my question correctly. In my case, I have more than ten pivot tables in a specific worksheet. It is hard to find the exact location of each one. I have to scroll horizontally and vertically in order to locate the pivot tables I am looking for. I thought Excel would list all pivot tables in a worksheet the way it lists all cell names that have been created.
Nothing automatic that I know of.
You either need to create something via code, such as a toolbar with pivot tables listed in a dropdown list.
Or simply add your own named range to a cell(s) by the pivot table.
Thanks, that's really helpful - do you know how you would modify this code to show the source data for each pivot table as well?
Very useful, thank you.
Just wanted to say that I found this question in a google search and it is awesome. The only change is that the editor is now Alt F11 at least for MSO 365
It always was actually. Alt+f8 is for the Macros dialog, so I think it was a typo on Andy's part.Originally Posted by Mr_Phil
Everyone who confuses correlation and causation ends up dead.
Sorry for resurrecting a very old thread; however the VBA code was so useful! Thank you.
I have added a small addition to display the name of each pivot table:
for each sht in worksheets:for each pt in sht.pivottables:debug.Print sht.name,pt.tablerange1.address, pt.name,pt.sourcedata:next pt:Next sht
There are currently 2 users browsing this thread. (0 members and 2 guests)
Posting Permissions
Register To Reply
Bookmarks