title: Passing worksheet as argument to a sub routine
body: <p>I know there are many questions out there on this topic yet I am still struggling with a subscript out of range? Yes the workbook I am importing does have a sheet entitled <code>LSL Recon</code>, I verified. I have debugged and substituted <code>LSL Recon</code> with <code>Import Sheets(1)</code> (as in <code>Sheet1</code>) and then the procedure does continue a bit further but does not import anything into the array.</p>
  
  <pre><code>Option Explicit
  
  Public FILENAME, c_DATE, cNAME As String
  'Public ws As Worksheet
  
  Sub main()
      Application.DisplayAlerts = True
      Application.ScreenUpdating = False
  
      CLEAR
      Import Sheets("LSL Recon")
      Display_Import
  End Sub()
  
  Sub Import(ws As Worksheet)  
      Workbooks.Open FILENAME
      Set TempBook = ActiveWorkbook
      ws.Activate
  
      cNAME = "Entity"
      cA = Sheets(1).Rows.Find(What:=UCase(cNAME), LookAt:=xlWhole, SearchDirection:=xlNext).Column
      cNAME = "Sector"
      cB = Sheets(1).Rows.Find(What:=UCase(cNAME), LookAt:=xlWhole, SearchDirection:=xlNext).Column
      cNAME = "Date"
      cC = Sheets(1).Rows.Find(What:=UCase(cNAME), LookAt:=xlWhole, SearchDirection:=xlNext).Column
      cNAME = "Client"
      cD = Sheets(1).Rows.Find(What:=UCase(cNAME), LookAt:=xlWhole, SearchDirection:=xlNext).Column
      ...
  End Sub()
  </code></pre>
  
  <p>Helpful Questions:<br>
  <a href="https://stackoverflow.com/questions/27109127/vba-calling-a-sub-on-another-worksheet-with-multiple-arguments">VBA: Calling a sub on another worksheet with multiple arguments</a><br>
  <a href="https://stackoverflow.com/questions/2082810/pass-sheet-to-a-function-excel-vba">pass sheet to a function (excel vba)</a><br>
  <a href="https://stackoverflow.com/questions/2376132/passing-a-worksheet-to-a-subroutine">Passing a Worksheet to a subroutine</a></p>