Excel - read the name from different sheets and return back to another sheet in sequence

[kmaungt]

Hi Excel experts ,kindly help to advise on this . For example , I have five names from other source and I wanted to return those name in sequence like in destination tab .


Below is my code in VBA , my problem can be solved by doing even and odd rows return if there's only two names . ( Please refer to 1st picture ) . But let's say I have five names or even more , but I wanted to return those name in sequence , how should I do it . ( 2nd picture ) Thanks in advance .

Sub AssignAgent()

Dim r As range
Dim i As Integer

For Each r In range("AE7:AE1000") 'Change this range

i = r.Row
If i Mod 2 = 1 Then 'this checks to see if i is odd
r.Cells.Value = "Mages"

Else
r.Cells.Value = "Yani"
End If

Next r

End Sub

 

[jolivanes]

Is this what you mean?
Change references as required.

Sub Maybe()
Dim sh1 As Worksheet, sh2 As Worksheet
Dim tms As Long
Set sh1 = Worksheets("Sheet1")
Set sh2 = Worksheets("Sheet2")
tms = sh1.Cells(sh1.Rows.Count, 1).End(xlUp).Row - 1
    sh1.Range("A2:A" & sh1.Cells(sh1.Rows.Count, 1).End(xlUp).Row).Copy sh2.Cells(1, 1).Resize(tms * tms)
End Sub