I am writing some macros in VBA for an Excel document, that uses a data base stored in 2 Worksheets, named “Datenbank” and “Stapler”. Here is some data stored. This 2 Worksheets are additionally stored in another Workbook stored in a server. There is a Worksheet in the Excel document, that calculate things using references from this 2 Worksheets used as the database. This calculations are of the type: = IF( F5 <> “”; XLOOKUP(F5; Stapler!B:B; Stapler!F:F; “”; 0); “”) or = IF( X5 <> “”; X5 * XLOOKUP(H5; Datenbank!F:F; Datenbank!H:H; 0; 0) / 1000; 0). The data base should be updatable from the server and you can just replace the existing worksheets “Datenbank” and “Stapler” with the new ones. The problem is, the references in the cells turn to “!REF#” (Error referencing) the moment you delete the old worksheets to replace them with the new updated ones, although the new ones have the exact same name and the old ones do not exist anymore. I assume this happens because Excel references the objects and not just their names, but if I could turn off this automatic update of references or do something to change the existing references to the new worksheets, it would be awesome. I have tried to change all formulas with Replace in Formula for all “UsedRange”, but that takes too long and also it doesn’t change the references made in some list dropdowns from data validation that also takes values from these 2 problematic worksheets.

Sub ImportDatenbankFunc()

' This Macro is responsible for the import of the Datenbank

    Dim Opfer1 As String, Opfer2 As String, Path As String, DatenbankPath As String, Version As String
    Dim Datenbank As Workbook, Rechner As Workbook
    
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
    Application.EnableEvents = False
    Opfer1 = "Datenbank"
    Opfer2 = "Stapler"
    
    ' Delete the existing worksheets where Data is stored
    Application.DisplayAlerts = False
    ThisWorkbook.Worksheets(Opfer1).Delete
    ThisWorkbook.Worksheets(Opfer2).Delete
    Application.DisplayAlerts = True
    
    ' Path of server where the new/updated version of the Data is stored
    Path = Dir("/Volumes/Topix-AFP/0 - Dateianhänge/Förderrechner/")
    Set Rechner = ActiveWorkbook
    
    Do While Path <> ""
    ' While the Path isnt empty (while there is still documents in the folder)
        If Left(Path, 9) = "Datenbank" Then
            ' If the first 9 letters of the document are "Datenbank", then open it
            DatenbankPath = "/Volumes/Topix-AFP/0 - Dateianhänge/Förderrechner/" & Path
            Set Datenbank = Workbooks.Open(DatenbankPath)
            Exit Do
        
        End If
        
        Path = Dir()
        
    Loop
    
    ' Copy the Worksheets"Datenbank" and "Stapler"
    Datenbank.Worksheets("Datenbank").Copy After:=Rechner.Sheets(Rechner.Sheets.count)
    Datenbank.Worksheets("Stapler").Copy After:=Rechner.Sheets(Rechner.Sheets.count)
    ' At this point, because there was no longer an existing "Datenbank" or "Stapler" Worksheets
    '  the new ones do not have the "(2)" after the name
    ' Read the version of the datenbank and write it down in a cell of "Versionierung"
    Version = Datenbank.Name
    Version = Right(Version, Len(Version) - 11)
    Version = Left(Version, Len(Version) - 5)
    Rechner.Worksheets("Versionierung").Range("I5").Value = Version
    Datenbank.Close savechanges:=False
    Application.Calculation = xlCalculationAutomatic
    Application.EnableEvents = True
    Application.ScreenUpdating = True

End Sub

' Attempt to dereference all cells, takes too long and doesnt dereference Data validation lists

Sub DeReference()

    Const RechnerWS As String = "Rechner (2)"
    Const OldRefStap As String = "Stapler"
    Const NewRefStap As String = "'Stapler (2)'"
    Const OldRefDB As String = "Datenbank"
    Const NewRefDB As String = "'Datenbank (2)'"

    Dim Zelle As Range
    For Each Zelle In ThisWorkbook.Worksheets(RechnerWS).UsedRange.Cells
    
        Zelle.Formula = Replace(Zelle.Formula, OldRefStap, NewRefStap)
        Zelle.Formula = Replace(Zelle.Formula, OldRefDB, NewRefDB)
        
    Next Zelle

End Sub

I have also looked for a similar approach using Replace for the formula of the Dropdown list, but haven´t found anything. In a worst-case scenario, this would also be helpful.