title: Dynamically reference different sheets in vba
body: <p>I am trying to reference different sheets in a macro without explicitly referencing them so as to make the macro compatible with any range of sheets. I have happily been using (ActiveSheet.Index +/- 1) for switching between sheets, but when it comes to entering formulae, I can't find anything to help me so far. </p>
  
  <p>The route I have gone down thus far is using a macro I found at: <a href="http://www.cpearson.com/excel/sheetref.htm" rel="nofollow noreferrer">http://www.cpearson.com/excel/sheetref.htm</a></p>
  
  <p>which references the name of the next sheet when entered in a cell. e.g. =NextSheetName() returns 'Sheet 2'.</p>
  
  <p>I combine that result with an ! mark and define it as nexxxtsheet. </p>
  
  <p>So when I enter the Formula </p>
  
  <pre><code>ActiveCell.FormulaR1C1 = "=" &amp; nexxxtsheet &amp; " "A1"
  </code></pre>
  
  <p>I expect the cell to have the formula: ='Name of next sheet'!A1</p>
  
  <p>However it returns ='Name of next sheet'!'A1' </p>
  
  <p>The difference being the quotes around A1 - rendering the formula useless. I am rather new to VBA and have no idea why this happens, and I am entirely certain that the method I am using to switch sheets without a direct reference is, for lack of a better term, rather crackhouse-style. I am very open to suggestions of how I should better switch between sheets, or corrections to render my formula useful. </p>
  
  <p>Thanks all!</p>