The value in the field should not disappear after refreshing a file

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim ShComments As Worksheet
    Dim ColCustomer As Integer
    Dim ColComments As Integer
    Dim r As Long
    If Target.Count > 1 Then Exit Sub
    With PivotTables("Pivot All GBP")
        ColCustomer = .PivotFields("Customer").LabelRange.Column
        With .TableRange1
            ColComments = .Columns.Count - .Column + 2
        End With
    End With
    If Application.Intersect(Target, Columns(ColComments)) Is Nothing Then Exit Sub
    On Error Resume Next
    Set ShComments = Worksheets("Comments")
    If Err <> 0 Then
        Err.Clear
        Set ShComments = Worksheets.Add(After:=Me)
        Me.Activate
        With ShComments
            ShComments.Name = "Comments"
            .Cells(1, 1).Value = "Customer"
            .Cells(1, 2).Value = "Comment"
        End With
    End If
    On Error GoTo 0
    With ShComments
        If WorksheetFunction.CountIf(.Columns(1), Cells(Target.Row, ColCustomer).Value) > 0 Then
            r = WorksheetFunction.Match(Cells(Target.Row, ColCustomer).Value, .Columns(1), False)
        Else
            r = .Cells(.Rows.Count, 1).End(xlUp).Row + 1
            .Cells(r, 1).Value = Cells(Target.Row, ColCustomer).Value
        End If
            .Cells(r, 2).Value = Target.Value
    End With
End Sub
Private Sub Worksheet_Calculate()
    Dim ShComments As Worksheet
    Dim RngCustomer As Range
    Dim ColComments As Integer
    Dim Cell As Range
    Dim r As Long
    On Error Resume Next
    Set ShComments = Worksheets("Comments")
    If Err <> 0 Then
        Err.Clear
        Exit Sub
    End If
    On Error GoTo 0
    Application.EnableEvents = False
    With PivotTables("Pivot All GBP")
        Set RngCustomer = .PivotFields("Customer").DataRange
        With .TableRange1
            ColComments = .Columns.Count - .Column + 2
        End With
    End With
    With ShComments
        For Each Cell In RngCustomer
            If WorksheetFunction.CountIf(.Columns(1), Cell.Value) > 0 Then
                r = WorksheetFunction.Match(Cell.Value, .Columns(1), False)
                Cells(Cell.Row, ColComments).Value = .Cells(r, 2).Value
            Else
                Cells(Cell.Row, ColComments).Value = ""
            End If
        Next Cell
    End With
    Application.EnableEvents = True
End Sub