Macro to copy data multiple times

There could be an easier way to do this, but this should work for you

Sub makelist()
Dim arSheet1(), arSheet2() As String
Dim str1, str2

ActiveWorkbook.Sheets(1).Select

ReDim arSheet1(1 To Range("A100000").End(xlUp).Row)
For r = 1 To Range("A100000").End(xlUp).Row
    arSheet1(r) = Range("A" & r).Value
Next

ActiveWorkbook.Sheets(2).Select
ReDim arSheet2(1 To Range("A100000").End(xlUp).Row)
For r = 1 To Range("A100000").End(xlUp).Row
    arSheet2(r) = Range("A" & r).Value
Next

ActiveWorkbook.Sheets(2).Select
r = 1
For Each str2 In arSheet2()
    For Each str1 In arSheet1()
        Range("A" & r).Value = str2 & " " & str1
        r = r + 1
    Next
Next
End Sub

i tihnk i just misjudged your desired output, try this

Sub makelist()
Dim arSheet1(), arSheet2() As String
Dim str1, str2

ActiveWorkbook.Sheets(1).Select

ReDim arSheet1(2 To Range("A100000").End(xlUp).Row)
For r = 2 To Range("A100000").End(xlUp).Row
    arSheet1(r) = Range("A" & r).Value
Next

ActiveWorkbook.Sheets(2).Select
ReDim arSheet2(2 To Range("A100000").End(xlUp).Row)
For r = 2 To Range("A100000").End(xlUp).Row
    arSheet2(r) = Range("A" & r).Value
Next

ActiveWorkbook.Sheets(3).Select
r = 2
For Each str2 In arSheet2()
    For Each str1 In arSheet1()
        Range("A" & r).Value = str2
        Range("B" & r).Value = str1
        r = r + 1
    Next
Next
End Sub

you last data set started on row 2 so i edited the macro to start at row 2, bellow is code to start at row 1

Sub makelist()
    Dim arSheet1(), arSheet2() As String
    Dim str1, str2
    
    ActiveWorkbook.Sheets(1).Select
    
    ReDim arSheet1(1 To Range("A100000").End(xlUp).Row)
    For r = 1 To Range("A100000").End(xlUp).Row
        arSheet1(r) = Range("A" & r).Value
    Next
    
    ActiveWorkbook.Sheets(2).Select
    ReDim arSheet2(1 To Range("A100000").End(xlUp).Row)
    For r = 1 To Range("A100000").End(xlUp).Row
        arSheet2(r) = Range("A" & r).Value
    Next
    
    ActiveWorkbook.Sheets(3).Select
    r = 1
    For Each str2 In arSheet2()
        For Each str1 In arSheet1()
            Range("A" & r).Value = str2
            Range("B" & r).Value = str1
            r = r + 1
        Next
    Next
End Sub

the code in post # 4 is giving me your exact output, please see attached

I figured out where our disconect was, the code needs to be entered into a new module, not into the sheet code. when it is working in the sheet code it is only refering to one sheet the whole time, where as the macro is designed to loop through 3 sheets.


try putting the code in a new module and let me know if that works for you.