Macro to transfer data as values to another sheet based on the result from a formula

Hi,

I found a macro (created by ky095n) in one forum that I tweaked so I can use it to transfer data from one sheet to another sheet (named: Closed) then delete that data in main sheet.
My problem is that I want the transferred data as values (no formulas).
Also, right now the code works fine if I type the text "Closed" but if the text is a result of a formula, the code doesn't work.
I am new in excel.
Hope I was able to explain my problem.
Thanks in advance.

Option Explicit
Dim LastRow As Range
Private Sub Worksheet_Change(ByVal Target As Range)


If Target.Text = "Closed" Then
     Target.EntireRow.Copy Sheets("Closed").Range("a20").End(xlUp).Offset(1, 0)
          Target.EntireRow.Delete
               Application.CutCopyMode = False
End If
End Sub

For copying values, not formulas, I did a copy-paste special with the macro recorder and then edited the code generated to remove the Select and Selection parts. The macro recorder is the best way to generate code if you are not sure of the syntax or Excel object names.

In which column is the text "Closed" the result of a formula? In the following code I've assumed it is column A.

Private Sub Worksheet_Change(ByVal Target As Range)

    Static ignoreEvent As Boolean
    
    If Not ignoreEvent Then
        With Target
            If .Parent.Cells(.Row, "A").Text = "Closed" Then
                .EntireRow.Copy
                Sheets("Closed").Range("A20").End(xlUp).Offset(1, 0).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone
                Application.CutCopyMode = False
                ignoreEvent = True  'Next statement makes recursive call to Worksheet_Change, so set flag to ignore it
                .EntireRow.Delete
                ignoreEvent = False
            End If
        End With
    End If
    
End Sub

If it isn't column A, change the "A" in the line | If .Parent.Cells(.Row, "A").Text = "Closed" Then | to the appropriate column letter.

It works for me. It always pastes to the first empty cell in column A on the Closed sheet (determined by looking in column A from A20 upwards). You can see which cell it will paste to by adding the following line before the PasteSpecial:

MsgBox "Paste to " & Sheets("Closed").Range("A20").End(xlUp).Offset(1, 0).Address