You say this is going to be shared, if this is on OneDrive then this probably won't work!
The x.xlsm and y.xlsx files have to be in the same location.
Add a sheet called Temp.
In the ThisWorkBook module
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
SendData
End Sub
In a Code module
Sub SendData()
Dim mb As Workbook, rng As Range
Application.ScreenUpdating = False
With Sheets("Summary")
.Range("A1:C8").Copy Sheets("Temp").Cells(1, 1)
For Each rng In .Range("A1:C8")
If rng.HasFormula Then rng.Formula = rng.Value
Next
End With
Set mb = Workbooks.Open(ThisWorkbook.Path & "\y.xlsx")
With mb
ThisWorkbook.Sheets("Summary").Range("A1:C8").Copy .Sheets("x").Cells(1, 1)
.Save
.Close
End With
Sheets("Temp").Range("A1:C8").Copy Sheets("Summary").Cells(1, 1)
End Sub