Macro changes vlookup formula. (Should not do so)

Hi,

When you insert new rows, the formulas will adjust to refer to the same actual cells as they were previously looking at and hence the formulas offset by 5 rows. You can fix that by copying the existing values down and then simply pasting the new data in.

Sub Paste_in_values_sheet()
'
' Paste_in_values_sheet Macro

Response = MsgBox("Paste to Values?", vbYesNo + vbExclamation, "Check")
 If Response = vbNo Then Exit Sub
    With Sheets("Values").Range("A1").CurrentRegion
        .Copy .Offset(5)
    End With
    Range("B2:C6").Copy Sheets("Values").Range("A1")
    
End Sub