Take active cells value and copy to another cell. Excel VBA

I am putting together a list to loop through in Excel.

I want to loop down column A starting at A2, and copy the value from the active cell to D2 (always D2)

This is what I have so far:

Sub Test2() Range("A2").Select Do Until IsEmpty(ActiveCell) 'Take value from active Cell and copy to D2 MsgBox ("Continue") ActiveCell.Offset(1, 0).Select Loop End Sub 

I tried replacing 'Take value from active Cell and copy to D2 with Range("D2).value = ActiveCell but this did not work.

Any suggestions.

So to recap. D2 will equal A2, I will get message box, press continue, then D2 will equal A3, and so on...

I have also tried:

Sub TestLoop() Dim Selected As Variant Selected = Selection.Value Range("A2").Select Do Until IsEmpty(ActiveCell) Range("D2").Value = Selected MsgBox ("Continue") ActiveCell.Offset(1, 0).Select Loop End Sub 

2 Answers

Just figured I'd add a simple version of your answer without the .select

Sub TestLoop() Dim r As Range Set r = Range("A2") Do Until IsEmpty(r) Range("D2").Value = r '.Value MsgBox ("Continue") Set r = r.Offset(1) Loop End Sub 

I have solved this.

I was placing the variable outside of the loop, I think.

Sub TestLoop() Range("A2").Select Do Until IsEmpty(ActiveCell) Dim Selected As Variant Selected = ActiveCell.Value Range("D2").Value = Selected MsgBox ("Continue") ActiveCell.Offset(1, 0).Select Loop End Sub