title: Trying to use VBA-excel to filter one worksheet based on clicked value in another workseet
body: <p>OK, I'm a total VBA noob, so excuse my awful code.</p>
  
  <p>I have two excel worksheets, one titled 'Contractors' and one titled 'Referring_to_Contractors'.</p>
  
  <p>The contractors sheet is laid out like so.</p>
  
  <p>Terr   ContractorID   First  Last<br>
  1            7         Bob    Smith<br>
  2            5         Jeff    Brown<br>
  3            8         Stan    Lee</p>
  
  <p>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.</p>
  
  <p>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"</p>
  
  <p>So, on the first sheet 'Contractors', I have: </p>
  
  <pre><code>Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
  
  End Sub
  </code></pre>
  
  <p>and..</p>
  
  <pre><code>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
  </code></pre>
  
  <p>Conceptually this seems like it should be pretty simple yet for some reason I can't get the second sheet to filter correctly.</p>
  
  <p>Any helps or even useful links would be greatly appreciated.</p>