Double Click/Filtering on another sheet

[sturnusek]

Hi there,

I would like to double click on a value within a sheet called Capacity, for it to be filtered out on another sheet, called Status. In Status, I have a table range already done called Details, and the column to be searched from is B4,

any ideas?

Thanks

 

[sturnusek]

I managed to solve my problem, code is listed below if anyone ever encountered a request like mine.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)

Dim strCellVal As String
Dim i As Long


On Error Resume Next


If Target.Cells.Count <> 1 Then Exit Sub


If Not Intersect(Target, [Projects[Region]]) Is Nothing Then
strCellVal = Target.Value
With Sheet2
.ListObjects(1).Range.AutoFilter 1, Criteria1:=(Target)
.Activate
i = .ListObjects(1).Range.Columns(1).SpecialCells(xlCellTypeVisible).Count
If i = 1 Then
.ListObjects(1).Range.End(xlDown).Offset(1, 2).Value = strCellVal
End If
.[B4].Select
End With
Cancel = True
End If


If Not Intersect(Target, [Projects[OtherField]]) Is Nothing Then
strCellVal = Target.Value
With Sheet2
End With
Cancel = True
End If


End Sub