Macro 74: Automatically Delete Pivot Table Drill- Down Sheets
One of the coolest features of a PivotTable is that it gives you the ability to double-click on a number
and drill into the details. The details are output to a new sheet that you can review. In most cases,
you don’t want to keep these sheets. In fact, they often become a nuisance, forcing you to take the
time to clean them up by deleting them.
This is especially a problem when you distribute PivotTable reports to users who frequently drill into
details. There is no guarantee they will remember to clean up the drill-down sheets. Although these
sheets probably won’t cause issues, they can clutter up the workbook.
Here is a technique you can implement to have your workbook automatically remove these drill-
down sheets.
How it works
The basic premise of this macro is actually very simple. When the user clicks for details, outputting a
drill-down sheet, the macro simply renames the output sheet so that the first ten characters are
PivotDrill. Then before the workbook closes, the macro finds any sheet that starts with PivotDrill and
deletes it.
The implementation does get a bit tricky because you essentially have to have two pieces of code.
One piece goes in the Worksheet_BeforeDoubleClick event, whereas the other piece goes
into the Workbook_BeforeClose event.
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As
Boolean)
‘Step 1: Declare your Variables
Dim pt As String
‘Step 2: Exit if Double-Click did not occur on a
PivotTable On Error Resume Next
If IsEmpty(Target) And ActiveCell.PivotField.Name <> “”
Then Cancel = True
Exit Sub
End If
‘Step 3: Set the PivotTable object
pt = ActiveSheet.Range(ActiveCell.Address).PivotTable
‘Step 4: If Drilldowns are Enabled, Drill down
If ActiveSheet.PivotTables(pt).EnableDrilldown Then
Selection.ShowDetail = True
ActiveSheet.Name = _
Replace(ActiveSheet.Name, “Sheet”, “PivotDrill”)
End If
End Sub
1. Step 1 starts by creating the pt object variable for our PivotTable.
2. Step 2 checks the double-clicked cell. If the cell is not associated with any PivotTable, we can- cel
the double-click event.
3. If a PivotTable is indeed associated with a cell, Step 3 fills the pt variable with the PivotTable.
4. Finally, Step 4 checks the EnableDrillDown property. If it is enabled, we trigger the
ShowDetail method. This outputs the drill-down details to a new worksheet.
The macro follows the output and renames the output sheet so that the first ten characters
are PivotDrill. We do this by using the Replace function. The Replace function replaces
certain text in an expression with other text. In this case, we are replacing the word Sheet
with PivotDrill: Replace(ActiveSheet.Name, “Sheet”, “PivotDrill”).
Sheet1 becomes PivotDrill1; Sheet12 becomes PivotDrill12, and so on.
Next, the macro sets up the Worksheet_BeforeDoubleClick event. As the name sug-
gests, this code runs when the workbook closes.
Private Sub Workbook_BeforeClose(Cancel As Boolean)
‘Step 5: Declare your Variables
Dim ws As Worksheet
‘Step 6: Loop through worksheets
For Each ws In ThisWorkbook.Worksheets
‘Step 7: Delete any sheet that starts with
PivotDrill If Left(ws.Name, 10) = “PivotDrill”
Then
Application.DisplayAlerts = False
ws.Delete
Application.DisplayAlerts = True
End If
Next ws
End Sub
5. Step 5 declares the ws Worksheet variable. This is used to hold worksheet objects as we loop
through the workbook.
6. Step 6 starts the looping, telling Excel we want to evaluate all worksheets in this
workbook.
7. In the last step, we evaluate the name of the sheet that has focus in the loop. If the left ten
characters of that sheet name are PivotDrill, we delete the worksheet. After all of the sheets
have been evaluated, all drill-down sheets have been cleaned up and the macro ends.
How to use it
To implement the first part of the macro, you need to copy and paste it into the Worksheet_
BeforeDoubleClick event code window. Placing the macro here allows it to run each time you
double-click on the sheet:
1. Activate the Visual Basic Editor by pressing ALT+F11.
2. In the Project window, find your project/workbook name and click the plus sign next to it in
order to see all the sheets.
3. Click on the sheet in which you want to trigger the code.
4. Select the BeforeDoubleClick event from the Event drop-down list box (see
Figure 6-4).
5. Type or paste the code.
Figure 6-4: Type or paste your code in the Worksheet_BeforeDoubleClick event code window.
To implement this macro, you need to copy and paste it into the Workbook_BeforeClose event
code window. Placing the macro here allows it to run each time you try to close the
workbook.
1. Activate the Visual Basic Editor by pressing ALT+F11.
2. In the Project window, find your project/workbook name and click the plus sign next to it in
order to see all the sheets.
3. Click ThisWorkbook.
4. Select the BeforeClose event in the Event drop-down list (see Figure 6-5).
5. Type or paste the code.
Figure 6-5: Enter or paste your code in the Workbook_BeforeClose event code window.
