VBA to create a dynamic rolling list of names

Question

I have a base list of staff names that are in a set order. For example:

1. Mathew
2. Mark
3. Luke
4. John

This list of names serves as the foundation for a "working list" of names that I use to generate monthly work rotas, filling each day of the month with names from the working list. To be honest, I pick up where I left off when I construct the following month, for example. Mark was on April 30; Luke will be on May 1. Then, my to-do list would appear as follows:

1. Luke
2. John
3. Mathew
4. Mark

At the moment I set the name of the person to start the next working list from and then I use formulas in the cells below to create the working list.

This works perfectly, but when the list grows, I have to manually alter the formulas to account for the change in the number of names in the list. This takes time, and there is a chance for error.

I'm searching for a VBA solution that would enable me to generate my working list without the use of formulae, and that would also react dynamically to the expansion or contraction of my base list.

Any thoughts?

Answer 1

If I understand you correctly, a case. It's crucial to note that the red cells are worthless. Just add new names after the last name. This is just a basic illustration of an active sheet.

Sub RollIt()
Dim ra As Range, i As Long, v() As Variant
Dim temp As String
Set ra = Range("B2").CurrentRegion
v = ra.Value
temp = v(1, 1)
For i = 2 To UBound(v)
v(i - 1, 1) = v(i, 1)
Next i
v(UBound(v), 1) = temp
ra.Value = v

End Sub