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.
I've reduced the problem to a single workbook with 2 sheets. Sheet1 cell A1 references Sheet2 cell A1 which holds the string "Sheet2A1CellData"
Everything commented out below has been tried including Application.Volatile and Application.Calculation.
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
Sheet1 A1 was originally =Sheet2!A1. When I run the function above from the VBE, Sheet1 cell A1 is set to =#REF!A1.
How can I keep the reference valid after the sheet has been replaced?
Obviously, the real world problem is much larger and re-importing CSV data requires updating 132,000 cells. 6000 rows x 22 Columns.
Thanks for any help.