title: Using VBA to change source links within sheets instead of workbook
body: <p>I'm currently attempting to write some VBA into a userform that will allow me to change say all the external links in sheet1 to workbookA, then all the external links in sheet2 to workbookB, then all in sheet3 to workbookC etc. etc. </p>
  
  <p>I've used this code to do all the links in the workbook</p>
  
  <pre><code>Private Sub btnUpdate_Click()
  
  Dim Source As String
  
  Application.ScreenUpdating = False
  currentsource = ActiveWorkbook.LinkSources(xlExcelLinks)
  
      'This is just a record of the changing source files
       Sheets("Notes").Range("C2") = currentsource
  
      'txtDirectory is the new source location chosen in my userform 
       Sheets("Notes").Range("C3") = txtDirectory
  
  Source = Sheets("Notes").Range("C2")
  
  ActiveWorkbook.ChangeLink Name:=Source, NewName:=txtDirectory, Type:=xlExcelLinks
  
  Application.ScreenUpdating = True
  
  End Sub
  </code></pre>
  
  <p>This works perfectly, however I now need to adapt it to change the links depending on the sheet I'm in.</p>
  
  <p>I've done a lot of googling to no avail, I've also spent quite a bit of time with my head in my VBA books.</p>
  
  <p>Any help would be greatly appreciated.</p>