foreach - Copy and Paste Loop

Question

I have two sheets: Sheet 1 and Sheet 2.

• Sheet 1 only has one column (Column A) with multiple rows. Product 1, Product 2...etc.
• Sheet 2 has multiple rows and columns.
• The goal is for each cell in Sheet 1 (starting with A2), copy and paste all of Sheet 2 onto a new sheet, Sheet 3. Loop until a blank cell in Sheet 1.

So for example: Product 1 would appear x times in Column A with the respective rows in Sheet 2. Then Product 2 would appear x times underneath Product 1, with the same respective rows in Sheet 2.

Below is a very rough macro of what I am wanting to do.

Sub Copy_Paste_Loop()
'
' Copy_Paste_Loop Macro
'

'
    Range("B2:C2").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    Sheets.Add After:=ActiveSheet
    Range("B1").Select
    ActiveSheet.Paste
    Selection.Columns.AutoFit
    Sheets("Sheet2").Select
    Range("B3").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Sheet2").Select
    Range("B2:B3").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Sheet3").Select
    Range("A1").Select
    ActiveSheet.Paste
    Range("A2").Select
    Application.CutCopyMode = False
    Selection.AutoFill Destination:=Range("A2:A106")
    Range("A2:A106").Select
    Range("B2").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    Selection.End(xlDown).Select
    Range("B107").Select
    ActiveSheet.Paste
    Sheets("Sheet2").Select
    Range("B4").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Sheet3").Select
    ActiveWindow.SmallScroll Down:=9
    Range("A107").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    Selection.AutoFill Destination:=Range("A107:A211")
    Range("A107:A211").Select
End Sub

question from:https://stackoverflow.com/questions/65945326/copy-and-paste-loop

Answer 1

Waitting for answers