title: Excel linked IF statement loses cell range following macro update
body: <p>I've created a basic macro within a workbook to clear data from a set number of tabs then copy in refreshed data from external workbooks. There is a master data tab within the workbook that uses IF formulas to obtain various stock information for that tab which then feeds through to other sheets. 
  E.G. </p>
  
  <pre><code>=IF($A$2="","",SUMIF(Data_CoventryStock!$A:$A,Data!$A$2,Data_CoventryStock!$E:$E))
  </code></pre>
  
  <p>Currently when the macro runs it produces the desired result but the IF Formulas lose the reference to the range e.g. <code>$A:$A</code> becomes <code>#N/A!</code></p>
  
  <p>I've been looking online for a solution but am unable to see a suitable option. I am new to this area.</p>
  
  <pre><code>Sub Update()
  '
  ' Update Macro
  '
  Application.DisplayAlerts = False
  
  ' Clears data from tabs
      Sheets("Data_10Day").Select
      Columns("A:B").Select
      Selection.Delete Shift:=xlToLeft
      Sheets("Data_CoventryStock").Select
      Columns("A:E").Select
      Selection.Delete Shift:=xlToLeft
      Sheets("Data_CowleyStock").Select
      Columns("A:E").Select
      Selection.Delete Shift:=xlToLeft
      Sheets("Data_RugbyStock").Select
      Columns("A:B").Select
      Selection.Delete Shift:=xlToLeft
      Sheets("Data_10Day").Select
  
  ' Copies data from other workbooks then pastes
  
      Workbooks.Open Filename:= _
      "C:\Users\ceasdown\Documents\HDS\Data\Data_10Day.xlsx"
      Range("A1").Select
      Range(Selection, Selection.End(xlDown)).Select
      Range(Selection, Selection.End(xlToRight)).Select
      Selection.Copy
      Windows("Coventry Ordering Template2.xlsm").Activate
      Sheets("Data_10Day").Select
      Range("A1").Select
      Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
          :=False, Transpose:=False
      Workbooks("Data_10Day.xlsx").Close
  
  
  
      Workbooks.Open Filename:= _
      "C:\Users\ceasdown\Documents\HDS\Data\Data_CoventryStock.xlsx"
      Range("A1").Select
      Range(Selection, Selection.End(xlDown)).Select
      Range(Selection, Selection.End(xlToRight)).Select
      Selection.Copy
      Windows("Coventry Ordering Template2.xlsm").Activate
      Sheets("Data_CoventryStock").Select
      Range("A1").Select
      Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
          :=False, Transpose:=False
     Workbooks("Data_CoventryStock.xlsx").Close
  
  
  
     Workbooks.Open Filename:= _
      "C:\Users\ceasdown\Documents\HDS\Data\Data_CowleyStock.xlsx"
      Range("A1").Select
      Range(Selection, Selection.End(xlDown)).Select
      Range(Selection, Selection.End(xlToRight)).Select
      Selection.Copy
      Windows("Coventry Ordering Template2.xlsm").Activate
      Sheets("Data_CowleyStock").Select
      Range("A1").Select
      Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
          :=False, Transpose:=False
     Workbooks("Data_CowleyStock.xlsx").Close
  
  
      Workbooks.Open Filename:= _
      "C:\Users\ceasdown\Documents\HDS\Data\Data_RugbyStock.xlsx"
      Range("A1").Select
      Range(Selection, Selection.End(xlDown)).Select
      Range(Selection, Selection.End(xlToRight)).Select
      Selection.Copy
      Windows("Coventry Ordering Template2.xlsm").Activate
      Sheets("Data_RugbyStock").Select
      Range("A1").Select
      Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
          :=False, Transpose:=False
     Workbooks("Data_RugbyStock.xlsx").Close
  
     Application.DisplayAlerts = True
  
  End Sub
  </code></pre>
  
  <p>I need it to retain the cell range in the IF formula so no manual update after running the macro is required.</p>