Macro that changes cell after each copy and paste

[c0087]

A	B
1
12	=A2-A1
4	=A3-A2
5	=A4-A3
6	=A5-A6
7	=A6-A5
14	=A7-A6
	=A8-A7

-This is sheet 1. The results in column B are also in Sheet2 which is why I want to copy/paste Sheet2 in this macro.
-Manually, I would drag up from B8 to get this, then copy/paste Sheet2 to a new sheet, then change =A8-A7 to A8-A6, drag up, and repeat.
-I'm looking for a macro automatically change the formula in increments of one (i.e. A8-A6, A7-A5, A6-A4), then copy/paste Sheet2 to a new sheet (formats + values only), then A8-A5, A7-A4, A6-A3 ... copy/paste Sheet2, etc.
-I have thousands of rows, so ideally I would like some sort of Loop where I can put in the number of times to do this from the start

 

[c0087]

okay, one more thing: If "CD_4" is your
What is [@N1] referring to?

Column C in the table

 

[PeteWright]

Once again, what you wrote is not consistent.
One time you mention column B and C, the other time D and C and so on.
Since you did not provide any further data I have no idea how your table looks like.

Here is what I figured out for your last statement:

Public j As Integer
Sub Macro()
   Dim w1 As Worksheet, w2 As Worksheet, r1 As Range, r2 As Variant
   Dim n As Long, i As Long, f As String, x As String
   j = 2
   f = "=ABS(CD_4[@N1]-R[-*]C[-9])"
   Set w1 = ThisWorkbook.Sheets("Corp")
   Set w2 = ThisWorkbook.Sheets("ROC")
   Set r1 = w1.Range("L2:L1468")
   Set r2 = w2.Range("A1")
     
   On Error Resume Next
   n = Application.InputBox("How many iterations?", Type:=1)
   On Error GoTo 0
   For i = 1 To n
      x = Replace(f, "*", j)
      w1.Range("L1468").FormulaR1C1 = x
      r1.FillUp
      Sheets.Add after:=ActiveSheet
      r1.Copy
      r2.PasteSpecial Paste:=xlPasteFormats
      r2.PasteSpecial Paste:=xlPasteValues
      j = j + 1
   Next i
End Sub

I can't even guess what the output will be, so try it on a copy of your workbook.

It would be of great help if you provided all information needed.