Deleting Pivot Table Drilldown Sheets

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:

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

Lot’s of

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.

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

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

8 thoughts on “Deleting Pivot Table Drilldown Sheets

  1. Ooh, that’s a good one. In fact, I can’t only think of one situation where a new sheet would have a listobject on it at all – if your sheet.xltx has a listobject. What would the identifiable heading be? Something that’s identical to first row of a pivot table source?

    That fails is sheet.xltx has a listobject on it, but otherwise seems to work.

  2. If sheet.xltx has a listobject on it, you’d know what the first cell in its heading row is, and could check for that.
    If it’s not a match, add the prefix for a Show Details sheet.

  3. The below should also work

    Private Sub Workbook_NewSheet(ByVal Sh As Object)
    Dim pvtSht As Worksheet, ddSht As Worksheet
    Set ddSht = Sh
    Set pvtSht = Sheets(ddSht.Index + 1)
    If pvtSht.PivotTables.Count And ddSht.ListObjects.Count = 1 And InStr(1, ddSht.ListObjects(1).Name, “Table”) Then
    Sh.Name = “PivotDD_” & Sh.Name
    End If
    End Sub
    Private Sub Workbook_SheetDeactivate(ByVal Sh As Object)

    With Application
    .DisplayAlerts = False
    .EnableEvents = False

    If InStr(1, Sh.Name, “PivotDD_”) Then Sh.Delete

    .DisplayAlerts = True
    .EnableEvents = True

    End With

    End Sub

  4. On Second thoughts it better to check the table name after ensuring there is a table

    Private Sub Workbook_NewSheet(ByVal Sh As Object)
    Dim pvtSht As Worksheet, ddSht As Worksheet
    Set ddSht = Sh
    Set pvtSht = Sheets(ddSht.Index + 1)
    If pvtSht.PivotTables.Count And ddSht.ListObjects.Count = 1 then
    If InStr(1, ddSht.ListObjects(1).Name, “Table”) Then Sh.Name = “PivotDD_” & Sh.Name
    End if
    End Sub

  5. Here’s sam’s code:

  6. A little late to the party, but why don’t you just learn the shortcut [ALT + E + L] to delete the sheet. Just be cautious because you can’t undo.

    I use it a lot in combination with F11 to generate a quick chart that I delete after.


Posting code? Use <pre> tags for VBA and <code> tags for inline.

Leave a Reply

Your email address will not be published.