VBA copy sequence minus one and paste, loop?

[ruannpreger]

Hi,

I am trying to figure out on how to copy a 'range'/'list' in column A to the next column B and then copy the same range/list A minus one selection to the end of column B (after the first copy) and so on , until the range in A is finished/ has reached its end.

example
Col A
15901
14901
22001
21200

VBA should copy 14901, 22001, 21200 and paste (vertically) that in Col B, then copy 22001,21200 to Col B under the previous paste. And then copy 21200 and paste to Col B. The list would then be:
14901
22001
21200
22001
21200
21200

It all should happen just in one sheet, in the end to generate a list (the actual list is over 100 codes long)
Any help on this is highly appreciated.
thanks.

 

[Rick Rothstein]

Give this macro a try...

Sub Test()
  Range("A2", Cells(Rows.Count, "A").End(xlUp)).Copy Range("D1")
  Range("B2", Cells(Rows.Count, "B").End(xlUp)).Copy Cells(Rows.Count, "D").End(xlUp).Offset(1)
End Sub

 

[Fluff]

Hi & welcome to MrExcel.
How about

Sub ruannpreger()
   Dim i As Long
   Dim Rng As Range
   
   Application.ScreenUpdating = False
   Set Rng = Range("A2", Range("A" & Rows.Count).End(xlUp))
   For i = 1 To Rng.Rows.Count - 1
      Rng.Offset(i).Copy Range("B" & Rows.Count).End(xlUp).Offset(1)
   Next i
End Sub

 

[ruannpreger]

Thanks for the prompt replies! incredible.
The suggestion from Fluff worked for me.

 

[Fluff]

Glad we could help & thanks for the feedback.

 

[Rick Rothstein]

The suggestion from Fluff worked for me.

Just so I know... did my suggestion work for you or not? Or didn't you try it because Fluff's worked?

 

[ruannpreger]

Hi Rick, i have tried it. However it did copy but it was missing a 'loop' to repeat the sequence.