VBA nested Do While within a For Loop-CodePudding

I'm having some trouble with a nested Do While and For loop in VBA.

In essence, I have two tables. I am trying to repeat each row of the first table however many rows there are in the second table. The number of rows in the second table can vary with time, so unfortunately I can't hard code in the number of rows it has.

In its current state, the for loop only repeats correctly the first row of my table onto the sheet "Var Admin", and does not go to do the same with the second row on the first table.

Here's what I have tried so far:

    Sheets("Managers").Select
    Range("Table6").Select
    managers = ActiveSheet.UsedRange.rows.Count
    managers = managers - 1

    Set myTable = Worksheets("Var").ListObjects("var_no_format")

    my = 1
    looper = 1
    
    For Each lr In myTable.ListRows
        Do While looper <= managers
            lr.Range.rows.Copy Sheets("Var Admin").Range("A" & my)
            my = my   1
        looper = looper   1
        Loop
    Next lr

I would appreciate any help I could get on this. I am not the best at asking questions on this site, so if anything needs clarifying, I will get back to you ASAP.

CodePudding user response：

I have copied the values into an array for speed.

Option Explicit

Sub copyrows()
 
    Dim ws1 As Worksheet, ws2 As Worksheet, ws3 As Worksheet
    Dim managers As Long, tbl As ListObject
    Dim n As Long, i As Long, r As Long, c As Long
    Dim trow As ListRow, ar
    
    With ThisWorkbook
        Set ws1 = .Sheets("Managers")
        Set ws2 = .Sheets("Var")
        Set ws3 = .Sheets("Var Admin")
    End With
        
    managers = ws1.ListObjects("Table6").ListRows.Count
    Set tbl = ws2.ListObjects("var_no_format")
    ReDim ar(1 To managers * tbl.ListRows.Count, 1 To tbl.ListColumns.Count)
        
    i = 1
    For r = 1 To tbl.ListRows.Count
        For c = 1 To tbl.ListColumns.Count
            ar(i, c) = tbl.DataBodyRange(r, c).Value2
            For n = 1 To managers - 1
                ar(i   n, c) = ar(i, c)
            Next
        Next
        i = i   managers
    Next
    
    ' output
    ws3.Range("A1").Resize(UBound(ar), UBound(ar, 2)) = ar
    MsgBox "Table rows copied for " & managers & " times", vbInformation
    
End Sub