Can we write a VBA Code, that automatically overwrites cells values of a formula on workbook close?

Nishkarsh31 

In the Visual Basic Editor, double-click ThisWorkbook under Microsoft Excel Objects.

Copy the following code into the ThisWorkbook module:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    Dim rng As Range
    With Worksheets("Data")
        For Each rng In .Range(.Range("A2"), .Range("A1").End(xlDown))
            If rng.Value <= Date Then
                With rng.Offset(0, 4)
                    .Value = .Value
                End With
            End If
        Next rng
    End With
End Sub

The code will run automatically each time you save the workbook (including when you close it and click Yes to the Save prompt).