title: How to Recreate a Sheet and Keep References Valid?
body: <p>I have a client who is hand holding a bunch of worksheets that should be standardized. They are created from importing CSV files. Basically, I need to replace the current manual sheets while they are being referenced from another tab without breaking the current references.</p>
  
  <p>I've reduced the problem to a single workbook with 2 sheets. Sheet1 cell A1 references Sheet2 cell A1 which holds the string "Sheet2A1CellData"</p>
  
  <p>Everything commented out below has been tried including Application.Volatile and Application.Calculation.</p>
  
  <pre><code>Option Explicit
  Sub TestSheet2Delete()
    Dim TmpSheet2 As Worksheet: Set TmpSheet2 = Sheets("Sheet2")
  
    'Application.Volatile
  
    If TmpSheet2 Is Nothing Then
      Exit Sub
    End If
  
    'Application.Calculation = False
  
    Application.DisplayAlerts = False
    TmpSheet2.Delete
    Application.DisplayAlerts = True
  
    Set TmpSheet2 = Worksheets.Add(After:=Sheets("Sheet1"))
  
    If TmpSheet2 Is Nothing Then
      Exit Sub
    End If
  
    TmpSheet2.Name = "Sheet2"
    TmpSheet2.Range("A1").Value = "Sheet2A1CellData"
  
    'Application.Calculation = True
  End Sub
  </code></pre>
  
  <p>Sheet1 A1 was originally =Sheet2!A1. When I run the function above from the VBE, Sheet1 cell A1 is set to =#REF!A1. </p>
  
  <p>How can I keep the reference valid after the sheet has been replaced?</p>
  
  <p>Obviously, the real world problem is much larger and re-importing CSV data requires updating 132,000 cells. 6000 rows x 22 Columns. </p>
  
  <p>Thanks for any help.</p>