Hi,
Here's a code that may work. However, it currently looks at "April" and "Accounting" only.
You mentioned "Value = 37" I do not know what you mean by that.
Copy and paste this code to the sheet module for "Overhead".
Code:
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
''Name variables
Dim MyMth As String
Dim MyYr As String
Dim MyCtr As String
Dim TDws As Worksheet
Dim OAws As Worksheet
Set TDws = Sheets("TranDetail")
Set OAws = Sheets("Overhead Analysis")
'''check to see if user clicks with in column "A"''
'' if not exit sub ''
If Intersect(Target, Range("A3:a23")) Is Nothing Then
Exit Sub
Else
''' we set the filter for TranDetail then ''''
''' activate TranDetail sheet'''
MyMth = Month(OAws.Range("C5"))
MyYr = Year(OAws.Range("C5"))
MyCtr = OAws.Range("A" & ActiveCell.Row)
''' Activate TranDetail and set filter '''
TDws.Activate
On Error Resume Next '' check for filter if there goto next line of code''
Selection.AutoFilter
TDws.Range("A5:G5").Select '' we selected the range for the filter
With Selection
.AutoFilter Field:=2, Criteria1:=MyMth
.AutoFilter Field:=3, Criteria1:=MyYr
.AutoFilter Field:=5, Criteria1:=MyCtr
End With
End If
End Sub