I'm trying to create a PivotTable in which a double click on a value leads the user to the filtered source sheet with the rows that this value represents, rather than a new sheet with the underlying data.
This is how far I've gotten, but I'm having issues extracting the relevant row and column names / values, as well as the filters currently active in the pivottable.
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Dim rng As Range
Dim wks As Worksheet
Dim pt As PivotTable
' Based on http://stackoverflow.com/questions/12526638/how-can-you-control-what-happens-when-you-double-click-a-pivot-table-entry-in-ex
Set wks = Target.Worksheet
For Each pt In wks.PivotTables()
Set rng = Range(pt.TableRange1.Address)
If Not Intersect(Target, rng) Is Nothing Then
Cancel = True
End If
Next
' Source: http://www.mrexcel.com/forum/excel-questions/778468-modify-pivottable-double-click-behavior.html
On Error GoTo ExitNow
With Target.PivotCell
If .PivotCellType = xlPivotCellValue And _
.PivotTable.PivotCache.SourceType = xlDatabase Then
SourceTable = .PivotTable.SourceData
MsgBox SourceTable
' I found the sourcetable, how would I collect the row/column
' names and values in order to filter this table?
End If
End With
ExitNow: Exit Sub
End Sub
In order to filter the source sheet, I need to extract the following characteristics upon a double click:
- The filters active in the current PivotTable (the original** 'Fieldname' and the relevant filters)
- The original** headers and row names and values relevant to the aggregate being selected (e.g. FieldX = 2013, FieldY="X"), that will enable me to filter the source sheet and present the underlying rows.
** Note that I'm not sure if this is relevant, but I extensively stumble upon PivotTables in which the row names shown are not the same as those in the source datasheet (by manually renaming them in the PivotTable). Also, is it possible to extract the 'groupings' created in the PivotTables?
Using these characteristics, the VBA for locating the source data and applying the relevant filters should be relatively straightforward. In most cases, the source table is an 'Excel Table', if this is relevant.
Any help is greatly appreciated.
Using the snippets brought forward by Byron's answer, I came up with the following. It doesn't work with grouped columns, nor does it work with tables. For now, at least I can work with regular ranges and PivotTables with tidy source data.
I use the following code to call a second procedure, note that (by far) I'm not an expert in VBA; I just wanted this functionality in a spreadsheet I'm working on:
And the procedures that are called: