title: Overwrite double click action in a PivotTable to go to filtered source data
body: <p>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. </p>
  
  <p>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.</p>
  
  <pre><code>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
  </code></pre>
  
  <p>In order to filter the source sheet, I need to extract the following characteristics upon a double click:</p>
  
  <ul>
  <li>The filters active in the current PivotTable (the original** 'Fieldname' and the relevant filters)</li>
  <li>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.</li>
  </ul>
  
  <p>** 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?</p>
  
  <p>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.</p>
  
  <p>Any help is greatly appreciated.</p>