VBA to create a dynamic rolling list of names

0 votes

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?

Mar 23, 2023 in Others by Kithuzzz
• 38,010 points

1 answer to this question.

0 votes

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.

enter image description here

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
answered Mar 23, 2023 by narikkadan
• 63,420 points

Related Questions In Others

0 votes
0 answers

how to list the contents of a asset into an event

May 29, 2019 in Others by anonymous
0 votes
1 answer

How to apply zoom animation for each element of a list in angular?

Hey @Sid, do check if this link ...READ MORE

answered Jul 30, 2019 in Others by Vardhan
• 13,190 points
0 votes
1 answer

How to create a drop-down list in Excel?

Making a list of the items you ...READ MORE

answered Oct 9, 2022 in Others by narikkadan
• 63,420 points
0 votes
1 answer
0 votes
1 answer

Retrieve epay.info Balance with VBA and Excel

This code should log you in, provided ...READ MORE

answered Sep 5, 2018 in Blockchain by digger
• 26,740 points
0 votes
1 answer

How to load file to Excel Power query from SFTP site

Currently, I don't think there is a ...READ MORE

answered Dec 3, 2018 in Power BI by Upasana
• 8,620 points
0 votes
1 answer

Using VBA Excel to create a gramatically correct list

The Excel AND function is a logical ...READ MORE

answered Feb 9, 2022 in Others by gaurav
• 23,260 points
0 votes
2 answers

How to copy a formula horizontally within a table using Excel VBA?

Hi so basically, create an adjacent column ...READ MORE

answered Feb 16, 2022 in Others by Edureka
• 13,670 points
0 votes
1 answer

In a excel formula I need to create a list of names on one sheet based upon criteria/data of another sheet

The final formula is: =IF(ROWS($H$3:H3)<=$I$1,INDEX(Personnel! ...READ MORE

answered Nov 25, 2022 in Others by narikkadan
• 63,420 points
0 votes
1 answer

Excel VBA - Out of memory when create a dynamic array formula

Try this: Sub MyArray() Range("A1").Formula2 = "=R[2]C[2]:R[2]C[6]" End Sub But that ...READ MORE

answered Feb 9, 2023 in Others by narikkadan
• 63,420 points
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP