Deleting Pivot Table Drilldown Sheets

Sep09
by on September 9, 2016 at 1:53 pm
Posted In: Around the Web, PivotTables, VBA

I tried to make drilling into pivot tables better once upon a time. I failed. Earlier this week, I read Debra’s blog post about showing details and deleting the sheets later. It got me thinking.

The problem I have is that her solution (and many others) rely on the Before_DoubleClick event. As you might imagine, I don’t double click to show pivot table details. I press the context menu key and choose Show Details from the menu. I need a different event or to capture that context menu item. I don’t think there’s any event that will allow me to identify new sheets only when they come from showing details of a pivot table. It doesn’t matter. The better answer is create my own shortcut.

In my Auto_Open and Auto_Close procedures in my PMW:

Application.OnKey "^+d", "PTDrillDown"

Application.OnKey "^+d"

That’s Ctrl+Shift+D for the uninitiated. That will now run PTDrillDown

Public Sub PTDrillDown()
   
    Dim pt As PivotTable
       
    On Error Resume Next
        Set pt = ActiveCell.PivotTable
    On Error GoTo 0
   
    If Not pt Is Nothing Then
        If Not Intersect(ActiveCell, pt.DataBodyRange) Is Nothing Then
            ActiveCell.ShowDetail = True
       
            On Error Resume Next
                Application.DisplayAlerts = False
                    ActiveWorkbook.Sheets(gsDRILLSHEET).Delete
                Application.DisplayAlerts = True
            On Error GoTo 0
            ActiveSheet.Name = gsDRILLSHEET
        End If
    End If
   
End Sub

Lot’s of On Error's in there. That’s the sign of really tight code, you know. This determines if the ActiveCell is in a pivot table by trying to set a PivotTable variable. If it’s in a pivot table, it next checks to see if it’s in the body (as opposed to row or column headers or filters). If it’s in the body, the code shows the detail, deletes any sheet with my special name, and names the resulting sheet with my special name. The special name lives in my MGlobals module.

Public Const gsDRILLSHEET As String = "_PivotDrill"

And for the coup de grace, I have a class module that defines an Application variable WithEvents. I added this event procedure to it.

Private Sub mxlApp_SheetDeactivate(ByVal Sh As Object)
   
    If Sh.Name = gsDRILLSHEET Then
        Application.DisplayAlerts = False
            Sh.Delete
        Application.DisplayAlerts = True
    End If
   
End Sub

Whenever I switch off of the details sheet, it goes away. Now that’s keeping things tidy.

└ Tags: