Applying filter based upon ActiveCell on a different Worksheet

Question

Hello,

The following VBA code sits in a worksheet called "SCU" and the cell "C4" is the information I would like to filter on. When double clicking on "C4" this takes us through to the "Account Data" sheet and attempts to filter on the active cell, however as it's on a separate worksheet I get the wrong data in the filter (Not "C4" in "SCU) I was wondering if there is a way to ref the "SCU" worksheet?

Private Sub Worksheet_BeforeDoubleClick (ByVal Target As Range, Cancel As Boolean)
If Intersect(Target, Range("C4")) Is Nothing Then
Else
    Sheets("Account Data").Select
    Selection.AutoFilter Field:=1, Criteria1:=ActiveCell
End With
End If
End Sub

Hope you can help! Mike Foster

Answer 1

When you select the sheet, the ActiveCell changes.

Try

Criteria1:=Intersect(Target, Sheets("SCU").Range("C4"))

or alternatively use

Criteria1:=Sheets("SCU").Range("C4")

If this doesn't work because the filter cell is dynamic, then give more details so we can come up with a better solution.