Moving data

[Laddr]

Ok so this is my issue I need to have a list of names in one row and a list of names in the second row however the names in the second row need to move in a rotation. So name the in cell one would move to 2, 2 would go to 3 and 3 would go to 1. There will be more than three names rotating just used that as an example is there a way to do this please and thank you

 

[Cubist]

Please post a sample data with the expected result. Thanks.

 

[roelandwatteeuw]

=IF(ROW($A$2:$A$7) = ROW($A$2), OFFSET($A$2:$A$7, ROWS($A$2:$A$7)-1, 0), OFFSET($A$2:$A$7, -1, 0))

 

[Logit]

Option Explicit
Sub RotList()
Dim rng As Range, Rws As Long
Application.ScreenUpdating = False
If Range("A2") Is Nothing Then Exit Sub
    With Sheets("Sheet1")
        Set rng = .Range("A2:A10")
        Rws = rng.Rows.Count
        rng.Rows(1).Cut Destination:=rng.Rows(Rws).Offset(1)
      
        rng.Rows(2).Cut Destination:=rng.Rows(1)
        rng.Rows(2).Delete shift:=xlUp
        
    End With
Application.ScreenUpdating = True
End Sub