My Last Post on Filtering Pivottable Source Data
Based on the comments from these three posts, there appears to be exactly zero people interested in this topic, so I’ll make it my last post on the matter.
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 right 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 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
rSource.AutoFilter
‘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
End If
Next rData
‘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
End If
End If
End Sub
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
Exit For
End If
Next i
IsDataField = bDataField
End Function
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.
Hi Dick,
I must admit, I see articles on PivotTables and cringe.
I’ve re-read your previous 3 posts, and think you’re onto something!
I’ve set up a pivottable with two Date columns, each grouped by Year.
The properties of RowField (wks.PivotTables(1).RowFields) reveal interesting properties:
ItemPositionCaption
12Date
24Other Date
35Rep
41Years
53Years2
I’m probably missing something, but couldn’t you use the Position attribute to get at the proper Date column?
The user can change the Position by dragging the fields around, so it’s not predictable.
I find this all highly interesting and useful, (the final product and the process). Quite often my boss will be standing over my shoulder while having me manipulate a pivot table and then he’ll ask to see the actual underlying data for a particular intersection in the pivot table. More importantly, I’ve learned a lot of VBA working through Hector’s original code and your process here. It was great to print out all the posts and make notes as I go. I hate trying to learn by reading through VBA object model help files. It was a big help to have printouts of the example pivot table and layout wizard screen shots and make annotations of the various objects. Thank you very much for the posts. I’ll try to comment more. Not sure I have much to add yet beyond appreciation.
@Dick
I am totally intrigued in this topic, and have read your struggles with interest – I would love to get this feature working, but I do use grouped dates a lot so the solution would need to be handle them. I too cringe when working with pivot tables via vba – I have clients that I have successfully automated the pivot tables, but getting my head around them again is non-trivial. The double-click on a pivot cell to see the original data in the context of the other data is very valuable – maybe Microsoft will add it to the enhancement list
@Rob – it is possible to only have the years or months and not have the actual date field in the row area, so relying on the date appearing in the row section is not sufficient.
Charlie
I think I really must learn a bit more about pivottables.
I’m with Rob – above my understanding – but shows I have much to learn still.
Please do not give up on the topic, need to go through your previous posts first in order to assess the topic first, then I will give you a more detailed feedback.
Dick,
In your datasource, if in a data range, you need to add 2 helper columns. ‘_Row’, with each cell set to ‘=ROW()’ and ‘_Filter’, leave those cells blank for now. In the Thisworkbook module, add the folowing code below, with a slight varation of some of your code. This code was only tested in Excel 2007 and 2010. Do not use a table name as your data source in Excel 2010 since the data source column range is not included, but is in Excel 2007.
‘
‘ Note: Excel 2007 and 2010 create’s a range as a Table or ListObject when double-clicking inside a pivot
‘ table data field
Private Sub Workbook_NewSheet(ByVal Sh As Object)
Dim rng As Range, rngHRow As Range, rngData As Range, col As Long
Application.ScreenUpdating = False
If Sh.ListObjects.Count > 0 Then
Set rngData = mrngDataSource
‘Check to make sure _Row and _Filter col’s exist
With rngData
If WorksheetFunction.CountIf(.Rows(1), “_Row”) = 0 Or WorksheetFunction.CountIf(.Rows(1), “_Filter”) = 0 Then
MsgBox “Could not find ‘_Row’ or ‘_Filter’ column(s) in “ & rngData.Parent.Name
Exit Sub
End If
End With
With Sh.ListObjects(1)
If WorksheetFunction.CountIf(.HeaderRowRange, “_Row”) = 0 Or WorksheetFunction.CountIf(.HeaderRowRange, “_Filter”) = 0 Then
MsgBox “Could not find ‘_Row’ or ‘_Filter’ column(s) in “ & Sh.Name
Exit Sub
End If
End With
With rngData
Set rngHRow = .Columns(WorksheetFunction.Match(“_Filter”, .Rows(1), 0))
End With
‘ Reset _Filter col values to False
With rngHRow
.Parent.Range(.Cells(2), .Cells(.Rows.Count)).Value2 = False
End With
‘ Set only those cells in ‘_Rows’ col in datasource that are also in sh
For Each rng In Sh.ListObjects(1).ListColumns(“_Row”).DataBodyRange
rngHRow.Cells(rng.Value2) = True
Next rng
‘ Delete sh
Application.DisplayAlerts = False
Sh.Delete
Application.DisplayAlerts = False
‘ Reset the datasource so it has only one filter, at ‘_Filter’ col & set to True
With rngHRow
.Parent.Activate
On Error Resume Next
.Parent.ShowAllData
.AutoFilter field:=.Column, Criteria1:=True
End With
Set rng = Nothing: Set rngHRow = Nothing: Set rngData = Nothing
End If
End Sub
‘
Private Sub Workbook_SheetBeforeDoubleClick(ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean)
Dim x As PivotCell, n As Long
On Error Resume Next
Set x = Target.Cells(1).PivotCell
If Err.Number = 0 Then
Set mrngDataSource = Range(x.PivotTable.SourceData)
Else
Err.Clear
Set mrngDataSource = Nothing
End If
End Sub
Dick,
After much thought, I think this solution has a problem if you can’t guarantee the pivot cache is up to date with it’s underlying data source. For example, if the row count is smaller in the data source than in the pivot cache, an error will occur when trying to set the non-existant row to a TRUE value. Also, if at least one value in a row, that is referenced in the pivot table, is altered, then setting it TRUE means that you are referencing an invalid state between the Pivot Cache and it’s data source. There are other examples also. I think this is why Microsoft chose not to include this functionality.
I too find this topic very interesting. Thank you for all of your posts and hard work. Like Sean said, my boss wants to initially see everything in the pivot table but then double click and see the source data so I can edit it. ex. Change due dates, statuses, etc.
I can’t get Hector’s code working in Excel 2010. Too bad Excel doesn’t give you the option of viewing the source data when you double click.