Nested Loop to Copy List of People Each 12 Times VBA

[avd88]

Hi,

I have a worksheet with data on column A and Column F.
Data on column A is the the month of the year 1-12 repeated 50 times.

I have another worksheet with a list of 50 people and I need to copy each persons information 12 times in column B - E on the other worksheet.

Built a macro to do this but it doesn't seem to be working. It seems that the way I'm trying to pass the Range on the Copy statement is giving me an error. But even when I take that out the code is not actually behaving as I'd expect. I think my logic is correct and the issue has to do with the syntax. Any ideas?

Sub addDpRep()

Dim x As Integer
Dim y As Integer

'COUNT ROWS WITH DATA DP SHEET
x = Worksheets("DPs").Range("A:A").Cells.SpecialCells(xlCellTypeConstants).Count

'COUNT ROWS WITH DATA UPLOAD SHEET
y = Worksheets("FOR FILE").Range("B:B").Cells.SpecialCells(xlCellTypeConstants).Count

For i = 1 To x

    Worksheets("DPs").Range(Cells(i + 1, 1), Cells(i + 1, 4)).Copy
    For j = 1 To 12
        Worksheets("FOR FILE").Cells(y + j, 2).PasteSpecial Paste:=xlPasteValues
    Next j

Next i


End Sub

 

[avd88]

I did make a slight change to my code as I noticed variable y needed to be inside the loop. I tested with a set range and it worked. Now I just need to know how to pass the dynamic range I'm trying to pass above.

So this worked and copied the first person on the list all the way down.

Sub addDpRep()

Dim x As Integer
Dim y As Integer

x = Worksheets("DPs").Range("A:A").Cells.SpecialCells(xlCellTypeConstants).Count

For i = 1 To x

y = Worksheets("FOR FILE").Range("B:B").Cells.SpecialCells(xlCellTypeConstants).Count

    Worksheets("DPs").Range("A2:D2").Copy
    For j = 1 To 12
        Worksheets("FOR FILE").Cells(y + j, 2).PasteSpecial Paste:=xlPasteValues
    Next j

Next i


End Sub

 

[avd88]

I ended up fixing it and it also looks like the issue is that you can't pass the range if you are working with 2 separate sheets. So this is my new code"

Dim z As Integer
Dim w As Integer

z = Worksheets("MGRs").Range("A:A").Cells.SpecialCells(xlCellTypeConstants).Count

For i = 1 To z
Dim row As Integer
row = i + 1

w = Worksheets("FOR FILE").Range("B:B").Cells.SpecialCells(xlCellTypeConstants).Count
    'UPDATE - you can use the statement below if you add With at the beginning and end with End With
    'Worksheets("MGRs").Range(Cells(i + 1, 1), Cells(i + 1, 4)).Copy
    Worksheets("MGRs").Range("A" & row & ":D" & row).Copy
    
    For j = 1 To 12
        Worksheets("FOR FILE").Cells(w + j, 2).PasteSpecial Paste:=xlPasteValues
    Next j

Next i

End Sub