Rename Pivot Table DrillDown Sheets

Rename Pivot Table DrillDown Sheets

When you double-click a pivot table value cell, Excel adds a new sheet to your workbook, with the details for that summary value. To make it easy to find those sheets, and delete them later, use the latest version of my macros to rename Pivot Table DrillDown sheets.

Show Details Command

Another way to create a DrillDown sheet is to  right-click a pivot table value cell, and click the Show Detail commands.

drilldownsheets01

Turn On or Off

There is a setting in PivotTable options, on the Data tab, where you can turn off this feature, if you don’t want people to use it.

In this screen shot, I’ve removed the check mark, so a message will appear if anyone double-clicks a value cell.

drilldownsheets02

Show Details or Drilldown

If you record a macro while you change that setting, you’ll see that Excel calls the feature Drilldown in the background.

drilldownsheets03

That’s why I call these the DrillDown macros. And I capitalize the second D, just so it matches my initials!

DrillDown Macros – Old

Long ago, I posted a set of DrillDown macros, to help manage the details sheets. Instead of leaving Excel’s default names for the sheets, the macro adds a prefix, “XShow_” at the start of the name.

That makes it easy to find the sheets, and there is another macro that deletes all the sheets that have a name with that prefix.

Updated Macros

Last week, Rich E. asked a few questions about the old DrillDown macros, and that inspired me to take another look at them.

  • For the macro that names each sheet, Rich asked if the prefix could be different, and if the tab colour could be set.
  • For the Delete sheets macro, Rich asked about saving some or all of the sheets, when closing the workbook.

Those were great questions, and I created a new download file, with custom settings for the DrillDown macros.

DrillDown Macro Settings

Here are the custom setting cells on the sample file’s Admin sheet

drilldownsheets04

When a new drilldown sheet is created, the macro changes its name:

  • The new name starts with the prefix from cell C4.
  • Next is the number from C12, formatted as 3-digits.
  • It ends with the name of the pivot table that was double-clicked.

NOTE: The first 31 characters of that string are used as the new sheet name.

Other Options

The Admin sheet has a tab colour cell too, and the fill colour from that is used on the new sheet’s tab.

drilldownsheets05

You can also choose a location for the new sheet – make it the first or last sheet, or leave it where it is (Do not move)

The current setting is “Last”, so the new sheet is the last tab in this workbook.

drilldownsheets06

Macro to Delete the DrillDown Sheets

There is a Workbook Close procedure in the sample file, and it will delete All, Some or None of the DrillDown sheets.

drilldownsheets07

  • ALL – Each sheet with the designated prefix is deleted, and the Next Number is reset to 1
  • None – The drilldown sheets are left as is, with nothing deleted.
  • Some – The macro shows a message for each sheet that has the designated prefix, and asks if you want to delete it. The Next Number is not reset.

drilldownsheets08

DrillDown Sheet Info

Another feature in the new DrillDown macro is the information added to the right of the detail table.

It shows the date that the sheet was created, the pivot table name and the pivot table’s sheet name. The sheet name is hyperlinked, so you can get back there quickly.

drilldownsheets09

Get the Sample File

To see the macros to rename Pivot Table DrillDown sheets, go to the DrillDown page on my Contextures website.

In the Download section, click the link for the Custom settings workbook (the second sample file.)

The zipped workbook is in xlsm format, and contains the macros. Be sure to enable macros, if you want to test them.

_________________

 

 

 

 

 

 

3 thoughts on “Rename Pivot Table DrillDown Sheets”

  1. I just created a drilldown sheet by double-clicking a pivot table, the way one does. I looked at it and for some reason clicked Ctrl-z (Undo) a couple times. I then went to delete the drilldown sheet, as one does, at least one who doesn’t use your no-doubt excellent code, and noticed that there was no sheet to delete.

    I confirmed this behavior – Undo gets rid of the drilldown sheet. My question is, has that always been the case?

    1. Thanks, Doug, and I hadn’t noticed that before!
      There’s no Undo option in Excel 2003, after creating the drilldown sheet.
      There is an Undo in Excel 2010, and I don’t have Excel 2007 installed, to test that version.

      1. Thanks Debra. I guess it’s just never came up before. I’ve also recently noticed that you can undo refreshing pivot tables and, I think, data-connected tables. Pretty amazing!

        So I guess you could change your code to just Undo until the sheet is gone. 😉

Leave a Reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.