title: Excel - VBA switching workbooks
body: <p>I have 3 workbooks</p>
  
  <p><strong>source</strong> workbook</p>
  
  <p><strong>target</strong> workbook</p>
  
  <p><strong>reference</strong> workbook - (Containing the macro which visible across all workbooks)</p>
  
  <p>Is it possible to change switch between Active workbook ( <strong>target</strong> workbook) and ( <strong>source</strong> workbook which was active workbook). </p>
  
  <ul>
  <li><p>Activate doesn't seem to help me, I do not if this is a bug or what it is. I have stopped in this step for quite sometime now.</p></li>
  <li><p><em>This workbook</em> function takes me back to <strong>reference</strong> workbook.</p></li>
  </ul>
  
  <p>Hope my question is clear. Appreciate your help.</p>
  
  <pre><code>' My code is in a test macroworkbook
  ' I am having a workbook opened 1.xlsx
  ' Opening a workbook countrypricelist.xls
  'running the code from 
  
  Dim sourcewb As Workbook
  Dim targetWorkbook As Workbook
  Dim filter As String
  Dim filter2 As String
  Dim rw As Long
  Dim x As Range
  Dim y As Range
  
  
  Set sourcewb = ActiveWorkbook
  Set x = sourcewb.Worksheets(1).Range("A:F")
  Dim sourceSheet As Worksheet
  Set sourceSheet = sourcewb.Worksheets(1)
  MsgBox sourceSheet.Name
  x.Select
  
  MsgBox sourceSheet.Name
  
  x.Select
  
  
  MsgBox sourcewb.Name ' This gives me sourceworkbook name.
  
  
  filter = "(*.xls),*.xls"
  
  
  Caption = "Please Select an input file "
  
  
  Application.ScreenUpdating = False
  
  
  Filename = Application.GetOpenFilename(filter, , Caption)
  
  Set targetWorkbook = Application.Workbooks.Open(Filename)
  
  Set y = targetWorkbook.Worksheets(1).Range("A:F")
  
  y.Select
  
  Dim targetSheet As Worksheet
  
  Set targetSheet = targetWorkbook.Worksheets(1)
  
  MsgBox targetSheet.Name
  
  
  Set targetWorkbook = ActiveWorkbook  
  
  
   MsgBox targetWorkbook.Name 'This gives me target workbook name
  
  
  y.Select
  sourcewb.Activate
  
  MsgBox sourcewb.Name ' Source workbook becomes same as targeworkbook.
  x.Select 
  
  MsgBox sourcewb.Name &amp; " This is the source workbook "
  MsgBox targetWorkbook.Name &amp; " This is the target workbook "
  
  
  With sourcewb.Worksheets(1)
  For rw = 2 To Cells(Rows.Count, 1).End(xlUp).Row
      Cells(rw, 3) = Application.VLookup(Cells(rw, 2).Value2, x, 3, False)
      Cells(rw, 4) = Application.VLookup(Cells(rw, 2).Value2, x, 4, False)
      Cells(rw, 5) = Application.VLookup(Cells(rw, 2).Value2, x, 5, False)
  Next rw
  End With
  
  
  MsgBox "All required columns from source mapped to target file "
  
  MsgBox "Trying to map from target to source "
  
  Set sourcewb = ActiveWorkbook
  MsgBox ActiveWorkbook.Name
  
  Application.ScreenUpdating = False
  </code></pre>
  
  <p>So If I change the line sourcewb = Thisworkbook my reference is changed to source code to workbook which is not my desired workbook as it contains many other macros for other activities. Hope this is code is fine.</p>