How to copy one row to multiple rows?

Question

I have to build a list that combines processes and countries.

I have 114 processes and 55 countries.

I have to build the list like this:

• First process 55 times (55 countries)
• Second process 55 times (55 countries)
• ... etc.

Example:

I am thinking about this code:

Sub Copydata()
    
    Dim i As Long
    Dim y As Long
    Dim sht As Worksheet
    Dim lastrow As Long
    
    Set sht = ThisWorkbook.Sheets("Sheet2")
    lastrow = sht.Cells(sht.Rows.Count, "A").End(x1Up).Row
    
    Worksheets("Sheet2").Activate
    Dim A
    
    For i = 1 To lastrow
        For y = 1 To 55
            Sheet3.Cells(y, 1) = Sheet2.Cells(i, 1)
        Next y
    Next i
    
End Sub

This should add to Sheet 3, 55 times process 1 then move to process 2 etc. (I can handle the country part, afterwards.)

I get

runtime error 1004

Answer 1

There are a few issues with your code. The one that is causing your runtime error is a typo of the vba const xlUp which you have as x1Up (the number 1 instead of the letter l).

In addition, you can clean things up a bit by using your variables more consistently. Grab references to your sheets either through the Workbook or though the sheet code names. In your code you do both. I also like to see more explicit value assignment on the Cells objects. Instead of setting the cells equal to each other, set the Cell.Value. Here's the slight modifications that I suggest:

EDIT: I've updated the code to do what you are describing in your comment.

Sub Copydata()

    Dim i As Long
    Dim y As Long
    Dim sht2 As Worksheet
    Dim sht3 As Worksheet
    Dim lastrow As Long
    
    Set sht2 = ThisWorkbook.Sheets("Sheet2")
    Set sht3 = ThisWorkbook.Sheets("Sheet3")
    lastrow = sht2.Cells(sht2.Rows.Count, "A").End(xlUp).Row
    
    y = 1
    
    For i = 1 To lastrow
        For y = y To y + 54
            sht3.Cells(y, 1).Value = sht2.Cells(i, 1).Value
        Next y
    Next i

End Sub