In short, I wrote some code that subverts the default action of double clicking on a Pivot Cell (creating a new sheet with the underlying data) and instead filtering the source data where it lives. It worked reasonably well except for grouped dates. When a row or column contains a date and it is grouped, it is impossible to filter the source data column. There, I said it. The only way to determine if something is truly impossible is to declare it impossible. About nine times our of ten, someone will prove that it can be done. This will be a one-in-ten case. Gauntlet thrown.
My rambling analysis of why this is impossible starts with how Pivot Tables work. When Excel creates a PivotTable, it first creates a PivotCache that holds all the data. In this way, the PivotTable class doesn’t have to keep track of where the data came from (Excel range, External source, etc.). It has this PivotCache structure that it can rely on to be the same every time.
The PivotCache knows where it got the data so that it can refresh itself when the PivotTable tells it to. But it doesn’t know (or expose) the relationship between its fields and those in the source data. There’s really no reason it should, other than I want it to for this particular application.
If I click on “2010” when I’ve grouped InvoiceDate on Months and Years, there’s no way for me to know that 2010 relates to InvoiceDate or some other date field. Excel knows, it’s just not telling me. I attempted to brute-force it, but it would be faster to write out the source data by hand on a legal pad, so that’s out.
Finally I took a different tack. I let the PivotTable make its extra sheet, then used the data on that sheet to filter the source. This method has its own problems, but here’s the code.
Private mPivotTable As PivotTable
Private Sub Workbook_SheetActivate(ByVal Sh As Object)
Dim rCell As Range
Dim rData As Range
Dim vMin As Variant, vMax As Variant
Dim rSource As Range
Dim lOldCalc As Long
If Not mPivotTable Is Nothing Then
lOldCalc = Application.Calculation
Application.Calculation = xlCalculationManual
Set rSource = Application.Evaluate(Application.ConvertFormula(mPivotTable.SourceData, xlR1C1, xlA1))
rSource.Parent.AutoFilterMode = False
'Loop through the header row
For Each rCell In Intersect(Sh.UsedRange, Sh.Rows(1)).Cells
If Not IsDataField(rCell) Then
'initialize min and max to the first cell in the range
vMin = rCell.Offset(1, 0).Value
vMax = rCell.Offset(1, 0).Value
'set the min and max for the column
For Each rData In Sh.Range(rCell.Offset(1, 0), Sh.Cells(Sh.Rows.Count, rCell.Column).End(xlUp)).Cells
If Not IsError(rData.Value) Then
If rData.Value < vMin Then vMin = rData.Value If rData.Value > vMax Then vMax = rData.Value
'Set the filters, ingore errors
rSource.AutoFilter rCell.Column, " >= " & vMin, 1, " <= " & vMax End If Next rCell 'so it doesn’t run at next sheet activate Set mPivotTable = Nothing Application.Calculation = lOldCalc 'Delete the sheet created by double click Application.DisplayAlerts = False Sh.Delete Application.DisplayAlerts = True rSource.Parent.Activate End If End Sub Private Sub Workbook_SheetBeforeDoubleClick(ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean) On Error Resume Next Set mPivotTable = Target.PivotTable On Error GoTo 0 'Make sure it’s an Excel source and that the cell clicked 'is in the data area If Not mPivotTable Is Nothing Then If mPivotTable.PivotCache.SourceType <> xlDatabase Or _
Intersect(ActiveCell, mPivotTable.DataBodyRange) Is Nothing Then
Set mPivotTable = Nothing
Private Function IsDataField(rCell As Range) As Boolean
Dim bDataField As Boolean
Dim i As Long
bDataField = False
For i = 1 To mPivotTable.DataFields.Count
If rCell.Value = mPivotTable.DataFields(i).SourceName Then
bDataField = True
IsDataField = bDataField
Here’s the basics: The Workbook_SheetBeforeDoubleClick event fires when a cell is double-clicked. If that cell is within a Pivot Table, a module level variable is created to hold a reference to the PivotTable. I don’t “Cancel” the double click action causing a new sheet to be created and activated. When that happens, the Workbook_SheetActivate event is fired. It checks that module level variable to see if there’s a PivotTable in there. If so, the largest and smallest values from each column are captured and a Filter is applied to the source data with those values. Lastly, the module level variable is cleared and the sheet is deleted.
I ignore data fields for filtering, because that just don’t make any sense. The problem with this method is errors. When I capture the largest and smallest values, I ignore cells with errors, which can lead to results that aren’t accurate (more rows in the source data are shown than should be).
Thus ends my wholly unsatisfying journey through filtering source data based on a PivotCell double click. Back to potentially interesting Excel stuff tomorrow.