VBA to record cell reference values as text and record change

Hi,

I am a new to using VBA and was hoping that there was a simple solution to my issue. This is my first post so I appologize if the description is too long.

I need to record cell values (A1:A5) and paste them into empty cells. But, the cell values I need to copy are actually reference values to cells in a different sheet. I used the following VBA to automatically copy and paste the specific values anytime there was a change.

 Private Sub Worksheet_Calculate()
    Range("A1:A5").Copy
    Cells(Rows.Count, 1).End(xlUp)(2, 1).PasteSpecial xlPasteValues
    Application.CutCopyMode = False
End Sub

The code works well in a simple Excel worksheet (see attached). But, when I placed it my actual worksheet (which has many formulas), the macro runs repeatedly until there is a "Run-time error '28:Out of stack space" message.

My understanding is that the macro calculates every time there is a change in the Excel-which is well into the hundreds, even though I am only looking to capture changes in cells A1:A5. I have already tried to use a Change event instead of Calculate, to only looks for changes in specific cells. The code works perfectly when the cells are text. But, right now, my cells will always be reference values or formulas.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("A1:A5")) Is Nothing Then
    Range("A1:A5").Copy
    Cells(Rows.Count, 1).End(xlUp)(2, 1).PasteSpecial xlPasteValues
    Application.CutCopyMode = False
    End If
End Sub

Is there a macro that could copy the cells and paste them as text, and then perform my change macro? Any other suggestions would be appreciated.

Thank you in advance for your help.