title: Edit formulas of multiple workbooks to remove external references / change the reference to the local workbook
body: <p>I have copied a worksheet into a batch of workbooks (around 45).</p>
  
  <p>This worksheet contains formulas and references i.e. <code>=Sheetname!A1</code> and <code>=IF(Sheetname!A2=1, "this", "that").</code></p>
  
  <p>However when the sheet has been copied, these formulas have become </p>
  
  <pre><code>=[Original_Book.xlsm]Sheetname!$A$1
  </code></pre>
  
  <p>and</p>
  
  <pre><code>=IF([Original_Book.xlsm]Sheetname!A2=1, "this", "that").
  </code></pre>
  
  <p>I'd like a way to remove all of the "[Original_Book.xlsm]" references so that the formulas become local again.</p>
  
  <p>Actual examples (the above being for illustrative purposes only</p>
  
  <pre><code>=[Survey_Checker.xlsm]Output!D42
  </code></pre>
  
  <p>Should become</p>
  
  <pre><code>=Output!D42
  </code></pre>
  
  <p>Likewise:</p>
  
  <pre><code>=INDEX([Survey_Checker.xlsm]QR1!E3:[Survey_Checker.xlsm]QR1!E9,[Survey_Checker.xlsm]Output!$G$17,0)
  </code></pre>
  
  <p>Should become</p>
  
  <pre><code>=INDEX(QR1!E3:QR1!E9,Output!$G$17,0)
  </code></pre>
  
  <p>and so on.</p>
  
  <p>In fact, if there was a VBA method to remove every instance of the string "[Survey_Checker.xlsm]" from the worksheet (which is called "DB Output") in the cell range A1:PE5 across each file, I beleive that might work?</p>
  
  <p>EDIT: Every single column between A1 and PE5 has a reference forumla in that needs removing.</p>