Filter data using Pivot table clicks

Filter data using Pivot table clicks
10 April 2007

This macro allows you to double click on a cell in a pivot table and view the details in the data using auto-filter set in the data sheet.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Dim rowHead As Range
Dim colHead As Range
Dim r, c, rowsBeforeStart, colsBeforeStart As Integer
Dim rowFieldOnSheet, colFieldOnSheet As Integer
Dim rowStr, colStr As String
Dim pivotSheet, dataSheet As Worksheet

Cancel = True

Set pivotSheet = Sheet2
Set dataSheet = Sheet3

' Row/Column headers
Set rowHead = pivotSheet.Range("A6:A11") 'Pivot table row header
Set colHead = pivotSheet.Range("B5:P5") 'Pivot table column header
' Offset for header row/column
rowsBeforeStart = 5
colsBeforeStart = 1
' Corresponding columns in the datasheet
rowFieldOnSheet = 8
colFieldOnSheet = 6


'This code provided filtering on Sheets based in pivot table
r = Target.row - rowsBeforeStart
c = Target.Column - colsBeforeStart

If (r <> rowHead.Rows.Count) Then
    r = -1
End If

If (c <> colHead.Columns.Count) Then
    c = -1
End If

dataSheet.Activate
dataSheet.Range("A1").Select

If (r <> -1) Then
    rowStr = rowHead.Cells(r, 1).Value
    If (rowStr = "(blank)") Then rowStr = "="
    Selection.AutoFilter Field:=rowFieldOnSheet, Criteria1:=rowStr
Else
    Selection.AutoFilter Field:=rowFieldOnSheet
End If

If (c <> -1) Then
    colStr = colHead.Cells(1, c).Value
    If (rowStr = "(blank)") Then rowStr = "="
    Selection.AutoFilter Field:=colFieldOnSheet, Criteria1:=colStr
Else
    Selection.AutoFilter Field:=colFieldOnSheet
End If
End Sub