title: Referencing the sheet index in a formula
body: <p>Hopefully someone can help i am trying to reference the sheetcode rather than the sheet name as the sheet name changes depending on the workbook i am referencing.</p>
  
  <p>my current code that i have been using is this </p>
  
  <pre><code>'Control Card 3 extracting from a closed workbook in to my active sheet`
  
  Range("C1196:C1219").Formula = "='[ASE Template White Book.xlsx]Cntrl 3'!$C6"
  Range("D1196:D1219").Formula = "='[ASE Template White Book.xlsx]Cntrl 3'!$D6"
  Range("C1220:C1243").Formula = "='[ASE Template White Book.xlsx]Cntrl 3'!$C38"
  Range("D1220:D1243").Formula = "='[ASE Template White Book.xlsx]Cntrl 3'!$D38"
  </code></pre>
  
  <p>This works fine but i am trying to use </p>
  
  <pre><code>    'Control Card 3 
  Range("C1196:C1219").Formula = "='[ASE Template White Book.xlsx]Sheet (30)'!$C6"
  </code></pre>
  
  <p>or</p>
  
  <pre><code>Range("D1196:D1219").Formula = "='[ASE Template White Book.xlsx]Sheet 30'!$D6"
  </code></pre>
  
  <p>or</p>
  
  <pre><code>Range("C1220:C1243").Formula = "='[ASE Template White Book.xlsx].Sheet30'!$C38"
  </code></pre>
  
  <p>I am trying to extract the value of cells to my worksheet i am working on but as mentioned the sheet name will change depending on the closed workbook which changes as the original document is saved as ASE template white book.xlsx so i have a constant reference document for the code.</p>
  
  <p>any help aprreciated</p>