Trying to use VBA-excel to filter one worksheet based on clicked value in another workseet

OK, I'm a total VBA noob, so excuse my awful code.

I have two excel worksheets, one titled 'Contractors' and one titled 'Referring_to_Contractors'.

The contractors sheet is laid out like so.

Terr ContractorID First Last

1 7 Bob Smith

2 5 Jeff Brown

3 8 Stan Lee

The Referring_to_Contractors sheet has the same fields and layout as the Contractors sheet above, but also has additional columns for Referring Contractors, so it has columns titled "Ref_Contractor_Id", "Ref_First", "Ref_Last", etc.

What I'm trying to do is use VBA so that when someone double clicks a row in the "Contractors开发者_如何转开发" sheet, it will take the value in the Contractor_ID column, then look in the "Referring_to_Contractors" sheet and filter by all records in that sheet that have that value as Contractor_ID. Essentially, this would display referral information for the Contractor_ID clicked on the first sheet. I created a named range for the Contractor_ID field titled "PrimaryContractor"

So, on the first sheet 'Contractors', I have:

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

End Sub

and..

Sub Select_Ref_Contractors()

    ContractorId = Range("PrimaryContractor").Value

    With Sheets("Referring_to_Contractors")
        .Visible = True
        .Select
   End With

    ActiveSheet.Range("$B$10:$N$44163).AutoFilter Field: =1, Criteria1:= ContractorID

    Application.Goto Range("A1"), True

End Sub

Conceptually this seems like it should be pretty simple yet for some reason I can't get the second sheet to filter correctly.

Any helps or even useful links would be greatly appreciated.

I have just knocked this up in Excel 2007 and it seems to work

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

If Not Target.Cells.Count > 1 Then
    id = CStr(Selection)

    Sheet2.Activate
    Sheet2.Range("A1", "c4").AutoFilter 1, id
End If
End Sub

It is using the same table on both sheets as below and when you double click a cell on Sheet1, the BeforeDoubleClick event fires and puts you onto Sheet2 with the filter applied.

ID  ID2 Text
1   2   a
2   2   b
3   3   c