Excel: a simple Do-loop for copy and pasting and making a table

Question

I wish to do a simple (I think!) Do-loop for the following: My simple program works as I want it to, but I think there is an easier way as I wish to repeat it 1000 times. Perhaps using some kind of loop? Do-Loop or otherwise. In short I want to:

1. copy the contents from A1
2. Paste them into B1
3. copy cell C1 (it has a simple formula that acts on B1)
4. copy the 'value' (only) from C1 into D1 (which will be a table of such values)

I wish to do this for A1 to A1000 and get a table of values D1, D2, ..., D1000 (perhaps as a square table). Cells B1 and C1 remain fixed.

My program is below:

Sub PasteSpecial_Examples()

    Range("A1").Copy Range("B1")
    Range("C1").Copy
    Range("D1").PasteSpecial Paste:=xlPasteValues

    Range("A2").Copy Range("B1")
    Range("C1").Copy
    Range("D2").PasteSpecial Paste:=xlPasteValues

    Range("A3").Copy Range("B1")
    Range("C1").Copy
    Range("D3").PasteSpecial Paste:=xlPasteValues

    Range("A4").Copy Range("B1")
    Range("C1").Copy
    Range("D4").PasteSpecial Paste:=xlPasteValues


    Application.CutCopyMode = False

End Sub

Solution

Your want a simple For...Next loop:

Sub PasteSpecial_Examples()

  For i = 1 To 1000
    Range("A" & i).Copy Range("B1")
    Range("C1").Copy
    Range("D" & i).PasteSpecial Paste:=xlPasteValues
  Next i

  Application.CutCopyMode = False

End Sub

Comments

[Rajesh Sinha]

You mean to say you want to copy A1:A1000 and Paste them in D1:D1000 ?

[user869299]

No. That would just copy the same values of A1:A1000 into D1:D1000. When I copy A1 (say) into B1 I get a result in C1. It is the result in C1 I wish to copy into D1.