VBA nested Do While within a For Loop

Question

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

What can I try next?

CDP1802 · Accepted Answer · 2021-11-27 14:12:53Z

1

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

edited Nov 27, 2021 at 14:12

answered Nov 27, 2021 at 13:34

CDP1802

17.3k2 gold badges10 silver badges19 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

AleksLi Over a year ago

Hi! Thank you so much for your answer. Unfortunately, its not exactly what I need. This code copies the entire table into Var Admin n number of times, but what I need is row 1 of the table copied to Var Admin n number of times, then row 2 copied into Var Admin n number of times, and so on.

CDP1802 Over a year ago

@AleksLi Do you want to copy the formats as well or just the values ?

AleksLi Over a year ago

Just the values is fine.

CDP1802 Over a year ago

@AleksLi btw your code will work if you move the looper = 1 line to after For Each lr In myTable.ListRows

AleksLi Over a year ago

That's seems to have worked. Thank you so much!!!

Collectives™ on Stack Overflow

VBA nested Do While within a For Loop

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related