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:
|
1 2 3 |
Application.OnKey "^+d", "PTDrillDown" Application.OnKey "^+d" |
That’s Ctrl+Shift+D for the uninitiated. That will now run PTDrillDown
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 |
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
|
1 |
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.
|
1 |
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.
|
1 2 3 4 5 6 7 8 9 |
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.
A new Show Details sheet would have a table with a identifiable heading in cell A1. You could check for that.
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.
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.
good VBA coding mate! well done :D I’m going to test it out on my Excel files right now!
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
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
Here’s sam’s code:
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.