title: VBA Open another workbook and sheet with variables
body: <p>I could not find an answer to this yet:</p>
  
  <p>I have a sheet with the Customer name in Cell A2 and the product code in Cell B2.
  I have a file location "C:\Users\Reception\Documents\Shared\Item Master Data\Customer BOMS\" with a workbook for each Customer. In these workbooks, there is a tab per product code with the formulation to make the product.</p>
  
  <p>What I need to do is to tell the Macro to open the workbook for the specific customer in cell A2 and to then go to the tab in cell B2. </p>
  
  <p>I have been able to open the workbook, but not the sheet.
  Here is my code:</p>
  
  <pre><code>Range("A2").Select
  
  Dim CName As String
  
  Dim PCode As String
  
  Dim BOM As Workbook
  
  Dim ws As Worksheet
  
  CName = ActiveCell.Value
  
  PCode = ActiveCell.Offset(0, 1).Value
  
  Set BOM = Workbooks.Open("C:\Users\Reception\Documents\Shared\Item Master Data\Customer BOMS\" &amp; CName &amp; ".xlsm")
  
  Set ws = BOM.Sheets(Range(PCode))
  </code></pre>