Automatically Delete Pivot Table Drilldown Sheets

I don’t know about you, but my favorite thing to do is to pass off a built in Excel feature as one of my own genius report development ideas.

Me: “I made it so that you can double-click on any number inside the Pivot Table to drill into details”

Client: “ooh….nice feature Mike. You’re a genius!”

Me: “Well what can I say…I give because I love.”

.

Well that nifty trick of double-clicking inside a Pivot Table to drill down has a side effect; it leaves behind lots of worksheets. In most cases, we don’t want to keep these sheets. In fact, they often become a nuisance, forcing us to take the time to clean them up by deleting them. This is especially a problem when you distribute pivot table reports to users who frequently drill into details. There is no guarantee they will every clean up the drilldown sheets. Although it is unlikely these sheets will cause issues, they can clutter up the workbook.

.

Today, I’ll share with you a little VBA that removes the junk drill-down sheets left behind from double-clicking on a Pivot Table.

.

The basic premise of this VBA is actually very simple. When the user clicks into details, outputting a drill down sheet, the macro follows along and simply renames the output sheet so that the first 10 characters are “PivotDrill”. Then before the workbook closes, the macro will find any sheet that starts with “PivotDrill” and deletes it. The implementation does get a bit tricky because you essentially have to two pieces of code. One piece goes in the Worksheet_BeforeDoubleClick event, while the other piece goes into the Workbook_BeforeClose event.

.

The first thing you’ll need to do is enter this code into the Worksheet_BeforeDoubleClick event (for the sheet your Pivot Table sits in).

.

  1. Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
  2.  
  3. 'Declare you Variables
  4. Dim pt As String
  5.  
  6. 'Exit if Double-Click did not occur on a pivot table
  7.  
  8. On Error Resume Next
  9.  
  10. If IsEmpty(Target) And ActiveCell.PivotField.Name <>"" Then
  11.  
  12. Cancel = True
  13.  
  14. Exit Sub
  15.  
  16. End If
  17.  
  18. 'Set the pivot table object
  19.  
  20. pt = ActiveSheet.Range(ActiveCell.Address).PivotTable
  21.  
  22. 'If Drilldowns are Enabled, Drill down
  23.  
  24. If ActiveSheet.PivotTables(pt).EnableDrilldown Then
  25.  
  26. Selection.ShowDetail = True
  27.  
  28. ActiveSheet.Name = Replace(ActiveSheet.Name, "Sheet", "PivotDrill")
  29.  
  30. End If
  31.  
  32. End Sub

.

Next, we set up the Worksheet_BeforeClose event. As the name suggests, this code will run when the workbook closes.

.

  1. Private Sub Workbook_BeforeClose(Cancel As Boolean)
  2.  
  3. 'Declare you Variables
  4.  
  5. Dim ws As Worksheet
  6.  
  7. 'Loop through worksheets
  8.  
  9. For Each ws In ThisWorkbook.Worksheets
  10.  
  11. 'Delete any sheet that starts with PivotDrill
  12.  
  13. If Left(ws.Name, 10) = "PivotDrill" Then
  14.  
  15. Application.DisplayAlerts = False
  16.  
  17. ws.Delete
  18.  
  19. Application.DisplayAlerts = True
  20.  
  21. End If
  22.  
  23. Next ws
  24.  
  25. End Sub

.

.

At this point, each time the workbook closes, the “PivotDrill” sheets will be deleted.

No more junk sheets left over from overzealous managers clicking all over your Pivot Tables.

7 thoughts on “Automatically Delete Pivot Table Drilldown Sheets

  1. Jim Otto

    I used similar code on a few occasions. I tried to limit users interface with the workbook by hiding sheet tabs as well. Part of the code was to automatically add a button that would return the user to the main console worksheet. This way the user didn’t need to see the worksheet tabs to navigate the workbook. Their only navigation was predefined on the main console sheet.

    This intended navigation limitation resulted in the need to automatically delete the “drillsheets” to avoid workbook bloat.

  2. Charlie

    Great idea,

    Shouldn’t the pt variable be a pivottable object rather than a string – and then would it not make the subsequent code easier as you would only have to check pt.enabledrilldown?

Leave a Reply

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