title: Excel VBA: problem passing workbook, worksheet objects to subroutines
body: <p>I have a main routine that creates a new Workbook and dynamically associates new sheets with it (based on the data in a separate input workbook/sheet).</p>
  
  <p>Excerpting the main routine:</p>
  
  <pre><code>Sub MainRoutine()
      Dim NmOutBook As String
      NmOutBook = "Client1Output_" &amp; Format(CStr(Now), "yyyy_mm_dd_hh_mm")
  
      Dim PosSourceBk, TrnSourceBk, OutputBk As Workbook
      Set PosSourceBk = Workbooks.Open("U:\Documents\Implementations\Client1\Client1Positions.xlsx")
      Set TrnSourceBk = Workbooks.Open("U:\Documents\Implementations\Client1\TradeHistory_0301.xlsx")
      Dim TrnSrcSht, TrnOutSht, PriorTrnOutSht, PosOutSht As Worksheet
      Set TrnSrcSht = TrnSourceBk.ActiveSheet
  
      'Create workbook to store output sheets
      Set OutputBk = Workbooks.Add
  </code></pre>
  
  <p>...</p>
  
  <pre><code>If (SecNm &lt;&gt; PriorSecNm) Then
      Set TrnOutSht = OutputBk.Sheets.Add(after:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count))
      TrnOutShtName = CStr(SecNm) + "_b"
      TrnOutSht.Name = TrnOutShtName
      AddXactSheetHeaders OutputBk, TrnOutSht
  </code></pre>
  
  <p>I'm passing my output workbook object and the corresponding worksheet object to the subroutine AddXactSheetHeaders, which looks like this:</p>
  
  <pre><code>Sub AddXactSheetHeaders(ByVal wb, ByVal ws)
      With wb
          With ws
              Range("A1").Value = "TradeDate"
              Range("B1").Value = "SettleDate"
              Range("C1").Value = "Tran ID"
              Range("D1").Value = "Tranx Type"
              Range("E1").Value = "Security Type"
              Range("F1").Value = "Security ID"
              Range("G1").Value = "SymbolDescription"
              Range("H1").Value = "Local Amount"
              Range("I1").Value = "Book Amount"
              Range("J1").Value = "MOIC Label"
              Range("K1").Value = "Quantity"
              Range("L1").Value = "Price"
              Range("M1").Value = "CurrencyCode"
          End With
      End With
  End Sub
  </code></pre>
  
  <p>The result of this is that cells A1:M1 are updated, but in the wrong workbook / worksheet.</p>
  
  <p>I've tried passing the object references by reference; when I do this a different (but still wrong) workbook / worksheet is updated.</p>
  
  <p>I must be missing something obvious, but have no idea what it might be.</p>
  
  <p>Any assistance appreciated. Thanks.</p>