copy and paste values rolling columns macro

Sorry for the delay.

Maybe...

Sub CopyTwoCells()
Dim WS As Worksheet
Dim NextCol As Long
Set WS = Worksheets(1)

With WS
    NextCol = .Cells(7, .Columns.Count).End(xlToLeft).Column + 1
    
    .Cells(7, NextCol) = Sheet1.Range("N7")
    .Cells(8, NextCol) = Sheet1.Range("N8")
    .Cells(9, NextCol).Formula = "=(R7C" & NextCol & "/R7C" & NextCol - 1 & ")/R7C" & NextCol - 1
End With

End Sub