Delete Excel Drilldown Sheets Automatically

With the pivot table Show Details feature in Excel, a new sheet is inserted when you double-click on the value cell in a pivot table. It’s a great feature for drilling into the details, but you can end up with lots of extra sheets in your workbook.

showdetails03

Usually, you don’t 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 – XShow_ – when these detail sheets are created.

showdetails06

That prefix should make the sheets easier to find and delete.

Automatically Delete the Sheets

To make the cleanup task even easier, you can use event code to prompt you to delete those sheet when you’re closing the workbook.

showdetails04

If you click Yes, all the sheets with the XShow_ prefix are deleted. Then, click Save, to save the tidied up version of the workbook.

showdetails05

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 Pivot Table Drilldown sample file.

________________

You may also like...

2 Responses

  1. DJ Randolph says:

    the code isn’t working

  2. stephan says:

    DJR, save the file to your directory, then load it & try it. The macro fails if working with the download copy.

Leave a Reply to stephan Cancel reply

Your email address will not be published. Required fields are marked *