Sub SetSeatingPlan()
Dim rNames As Range 'Range containing list of names.
Dim rPlan As Range 'Seating Plan which is B2:E5 for me on another sheet
Dim c As Range ' used to loop through names
Dim iRows As Integer 'number of rows in the plan
Dim iColumns As Integer 'number of columns in the plan.
Dim LastNameRow As Integer 'row that contains last name in list assuming nothing else is in column
Dim i As Integer 'counter
Dim x As Integer 'counter
Dim y As Integer: y = 1 'counter
'get last used row if it is dynamic. can hard write if value never changes
LastNameRow = Sheets("Names").Range("A" & Sheets("Names").Rows.Count).End(xlUp).Row
'set the ranges assuming first name is in cell A2
Set rNames = Sheets("Names").Range("A2:A" & LastNameRow)
Set rPlan = Sheets("Seating Plan").Range("B2:E5")
'clear the seating plan
rPlan.ClearContents
'set the variables byu getting number of rows and columns
iRows = rPlan.Rows.Count
iColumns = rPlan.Columns.Count
'Loop through columns and then rows and enter the names from the cells
For i = 1 To iColumns
For x = 1 To iRows
'write the name in the cell
rPlan.Cells(x, i) = rNames.Cells(y, 1)
'if we have less names than seats then leave the loops
If y >= rNames.Cells.Count Then GoTo ExitSub
'got to next name
y = y + 1
Next x
Next i
ExitSub:
End Sub
