I have a worksheet with 12 people in column A and weekly date periods in row 1 for the column headings. I want to populate the worksheet with the 18 tasks that are stored in worksheet 2. I would like these tasks to snake through the columns and repeat sequentially until all 12 people and all 10 date ranges (120 cells total) is filled. I have attached an image to help clarify what I am trying to do.
I used an array to populate the tasks. I have a For loop for the total number of iterations, 120 in my situation. I am stuck on how to proceed and can't wrap my mind on the code I need to make this work. Do I need to use a two-dimensional array. Maybe an array within an array or something else? Thanks for any help.
Code:
Sub GenerateWeeklyTasks()
Dim Tasks() As String, nbrTasks As Integer, i As Integer, j As Integer, _
rngPeople As Range, nbrPeople As Integer, nbrDateRanges As Integer, _
rngDateRange As Range, nbrIterations As Integer, t As Integer, z As Integer
i = 1 'initialize the x coordinate of the Cells function to start in cell B2
j = 1 'initialize the y coordinate of the Cells function to start in cell B2
t = 1 'Initialize the index for the Tasks array
z = 1 'initialize the counter variable for the total number of iterations
'count the number of people to assign tasks to
Set rngPeople = Worksheets(1).Range("A2", Range("A2").End(xlDown))
nbrPeople = rngPeople.Count
nbrTasks = WorksheetFunction.CountA(Worksheets(2).Columns(1))
'count the number of date ranges
Set rngDateRange = Worksheets(1).Range("B1", Range("B1").End(xlToRight))
nbrDateRanges = rngDateRange.Count
nbrIterations = nbrDateRanges * nbrPeople
'Resize the array
ReDim Tasks(nbrTasks)
'Populate the array
For t = 1 To nbrTasks
Tasks(t) = Worksheets(2).Cells(i, j).Value
Debug.Print Tasks(t)
i = i + 1
Next t
'populate the worksheet
For z = 1 To nbrIterations
' Code goes here
Next z
End Sub