Populate worksheet with a list of Tasks

[ScottyBee75]

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.

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

[ScottyBee75]

[Logit]

Post a copy of your workbook

[ScottyBee75]

I took a different approach and used a nested FOR loop to complete my project. I have also attached the workbook to this post so others can follow along.

Sub AssignTasks()
    Dim namesRange As Range
    Dim tasksRange As Range
    Dim taskIndex As Integer
    Dim rowIndex As Integer
    Dim colIndex As Integer
    Dim taskCell As Range
    
    ' Define the ranges
    Set namesRange = Worksheets(1).Range("A2:A13")
    Set tasksRange = Worksheets(2).Range("A1:A18")
    
    ' Initialize the task index
    taskIndex = 1
    
    ' Loop through the columns (dates)
    For colIndex = 2 To 11 ' B to K (2 to 11)
        ' Loop through the names
        For Each taskCell In namesRange
            ' Assign task to the cell
            taskCell.Offset(0, colIndex - 1).Value = tasksRange.Cells(taskIndex, 1).Value
            
            ' Update the task index
            taskIndex = taskIndex + 1
            
            ' Reset the task index if it exceeds the number of tasks
            If taskIndex > tasksRange.Rows.Count Then
                taskIndex = 1
            End If
        Next taskCell
    Next colIndex
End Sub