When you double-click on the value cell in a pivot table, a new sheet is automatically inserted in the workbook. The new sheet contains a table, listing all the source data records that are summarized in the number that you double-clicked.
Double-clicking the cell is a shortcut to the pivot table Show Details command. You can also right-click on a value cell, and click Show Details.
Show Details, also called DrillDown, is a great feature for digging into the details, but you can end up with lots of extra sheets in your workbook.
However, you don’t usually want to save the sheets, so you manually delete them before you close the file.
Automatically Name the Sheets
With event code on the pivot table’s worksheet, and in the workbook module, you can add a prefix, such as “XShow”, when the Show Detail sheets are created.
That prefix should make the sheets easier to find and delete, before you close the workbook.
Automatically Delete the Sheets
To make the cleanup task even more efficient, you can use programming to show a message when you’re closing the workbook, if there are Show Details sheets in the file.
If you click Yes, all the sheets with the “XShow_” prefix are deleted, and the workbook stays organized. Then, save the tidied up version of the workbook, when prompted.
See the Drilldown Sheet Code
For detailed instructions on adding the drilldown sheet naming and deleting code, visit the Excel Pivot Table Drilldown page on the Contextures website.
Download the Sample Drilldown File
To see how the event code names the sheets, and deletes them when closing, you can download the sample file from my Contextures website, at the link shown above.
________________
I like the method to name pivot drill-down sheets, then delete them when closing the workbook. Good stuff.
Is there any way to change the default appearance of the Show Details Sheets? I would prefer the sheet have no color shading at all.