Copy / Paste Multiple Times in VBA

[Debating_Earth]

Hello All,

My apologies--I am very new to VBA.

I am trying to use the program to complete the following list of action items:

1. Create (and save) a new workbook
2. Copy 5 columns from the original file ('Wall^M Forecasting...') and paste into the new file (call it 'Simulated Jobs')
3. Repeat this paste process 100 times, stacking the 20 roes from the original copy, and pasting at the next blank row each time

I am basically halfway done--but I cannot crack step 3 outlined above. Here is what I have done so far: again, I am new to the program, so my apologies for the crude code:

This works perfectly for one scenario, or those first 20 rows. Any ideas on how to replicate this 100x? All suggestions would be greatly appreciated!

Thanks

 

[shknbk2]

Welcome to the forum. I think this will work if I understand your request accurately.

This copies "N3:R22" from the dashboard and pastes a first copy into B2 of Simulated_Jobs. Then, it pastes another copy of "N3:R22" to B22, another to B42, and on for 100 copies.

Sub Macro2()
    Dim TargetRange As Range
    Dim CopyCount As Integer
    Dim i As Integer
    
    'Change this value to the number of times you want to copy the data
    CopyCount = 100

    Workbooks.Add
        ActiveWorkbook.SaveAs "Simulated_Jobs.xlsx"
    
    Workbooks("Wall^M Forecasting Dashboard 210616 (PL edit v3)").Activate
        Sheets("SE Totals").Activate
        Range("N3:R22").Copy
    
    Workbooks("Simulated_Jobs.xlsx").Activate
        Set TargetRange = Range("B2")
        For i = 1 To CopyCount
            TargetRange.PasteSpecial xlPasteValuesAndNumberFormats
            Set TargetRange = Cells(Rows.Count, 2).End(xlUp).Offset(1, 0)
        Next
        Worksheets("Sheet1").Columns("B:F").AutoFit
        Worksheets("Sheet1").Columns("C:F").HorizontalAlignment = xlCenter
        Application.CutCopyMode = False
End Sub