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

 

[PeteWright]

@c0087 I am currently working on your Macro, but need some clarification.
Could you "manually" modify your Sheets and post the base Sheet and the result Sheets as Screenshots? (only a few rows to show what's going on)
I'm a little bit confused what should go where...

 

[c0087]

The values in sheet2 are just different subsets of column D in sheet1. The data will obviously change when the calculation for column D changes after each "run", which is why i need it copy/pasted as such

 

[PeteWright]

Alright, thanks for the info.
Just one more thing: What should the Macro exactly do?
Because your Screenshots look a little different from what I thought that the Macro should do.

May I ask you to write something like a step-by-step instruction of what should happen?
e.g. "copy cells in column B starting at B1 into Sheet2 B2 and change the formula from X to Y"

Sorry to ask

 

[c0087]

Alright, thanks for the info.
Just one more thing: What should the Macro exactly do?
Because your Screenshots look a little different from what I thought that the Macro should do.

May I ask you to write something like a step-by-step instruction of what should happen?

Sorry to ask

Basically just need the right side of the formula (i.e. B1298) to change by -1 each "run" and automatically "drag up", then copy/paste sheet2 (values + format only) to a new sheet and keep repeating.

Right now D1299 =ABS(B1299-B1298) .... I want it to change =ABS(B1299-B1297) and then automatically "drag up" to cell D1...then copy and paste Sheet2 to a new Sheet, then repeat ... =ABS(B1299-B1296), etc... it's imperative that it copies sheet2 to a new sheet each time, since the data will change on each new calculation.

 

[PeteWright]

Sorry to bother you, I know that for you it's all obvious but I still don't get what should happen. Right now I see too many variables.

The easiest thing to do would be if you record a Macro that does exactly what you want.
To do so launch the Macro Recorder and start doing what ever you need to do repeated for let's say 3 times. Than stop the Recorder and post the resulting code.

 

[c0087]

Sorry to bother you, I know that for you it's all obvious but I still don't get what should happen. Right now I see too many variables.

The easiest thing to do would be if you record a Macro that does exactly what you want.
To do so launch the Macro Recorder and start doing what ever you need to do repeated for let's say 3 times. Than stop the Recorder and post the resulting code.

Sub AutoDrag()
'
' AutoDrag Macro
'

'
    ActiveCell.FormulaR1C1 = "=ABS(CD_4[@N1]-R[-2]C[-9])"
    Range("L1468").Select
    Selection.AutoFill Destination:=Range("L2:L1468"), Type:=xlFillDefault
    Range("L2:L1468").Select
    Sheets("ROC").Select
    Cells.Select
    Selection.Copy
    Sheets.Add After:=ActiveSheet
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False
    With Selection
        .HorizontalAlignment = xlRight
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    Sheets("Corp").Select
    Range("L30").Select
    Selection.End(xlDown).Select
    ActiveCell.FormulaR1C1 = "=ABS(CD_4[@N1]-R[-3]C[-9])"
    Range("L1468").Select
    Selection.AutoFill Destination:=Range("L2:L1468"), Type:=xlFillDefault
    Range("L2:L1468").Select
    Sheets("ROC").Select
    Selection.Copy
    Sheets.Add After:=ActiveSheet
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False
    With Selection
        .HorizontalAlignment = xlRight
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    Sheets("Corp").Select
    Range("L20").Select
    Selection.End(xlDown).Select
    ActiveCell.FormulaR1C1 = "=ABS(CD_4[@N1]-R[-4]C[-9])"
    Range("L1468").Select
    Selection.AutoFill Destination:=Range("L2:L1468"), Type:=xlFillDefault
    Range("L2:L1468").Select
    Sheets("ROC").Select
    Selection.Copy
    Sheets.Add After:=ActiveSheet
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False
    With Selection
        .HorizontalAlignment = xlRight
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
End Sub

What I'm trying to accomplish is to keep looping this hundreds of times, with the "R" value changing each time (-3, -4, etc)

 

[PeteWright]

Sorry, I almost got it. Almost. But I think this way I can't figure out anything.
Your values / formulas / references are not consistent or not clear (not mentioned)

First you mentioned Column A and Column B, but then you posted a screenshot with Column B and Column D.
Then you start your Macro with Range "L1468", but later you choose Ranges "L30" and "L20".
Besides that, I don't know where your Table is located since you have a named Reference "CD_4[@N1]".
And your first increment (or decrement) starts with "-2" not with "-1"

I'm lost. Maybe you could upload your workbook.

I'm very close to get a working Macro, but I need some more information:
What is "CD_4[@N1]" (looks like a table)

 

[c0087]

Sorry, I almost got it. Almost. But I think this way I can't figure out anything.
Your values / formulas / references are not consistent or not clear (not mentioned)

First you mentioned Column A and Column B, but then you posted a screenshot with Column B and Column D.
Then you start your Macro with Range "L1468", but later you choose Ranges "L30" and "L20".
Besides that, I don't know where your Table is located since you have a named Reference "CD_4[@N1]".
And your first increment (or decrement) starts with "-2" not with "-1"

I'm lost. Maybe you could upload your workbook.

I'm very close to get a working Macro, but I need some more information:
What is "CD_4[@N1]" (looks like a table)

Yes i was using the screenshots as an example to make it easier since my workbook is too large to upload.. I thought the logic would be the same, forgetting that it was formatted as a table.
The L:20 and L:30 ranges are just cells i manually selected and then hit ctrl+down arrow to get down to the last number easier.
My table range is B1:H1468 named "CD_4" and yes the first decrement starts with -2. I just that need that -2 to decrement each time, then autofill range (L2:L1468( then copy/paste, repeat until it goes from -2 to -1468.

 

[PeteWright]

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