Copy / Paste Multiple Times in VBA

Debating_Earth

New Member
Joined
Jun 17, 2021
Messages
1
Office Version
  1. 365
Platform
  1. Windows
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:

1623988045278.png



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
 

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.

shknbk2

Active Member
Joined
Mar 5, 2016
Messages
386
Office Version
  1. 365
Platform
  1. Windows
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.
VBA Code:
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
 
Learn Excel from Bill Jelen

Understanding data is crucial, and the easiest place to start is with Microsoft Excel.

Forum statistics

Threads
1,151,605
Messages
5,765,385
Members
425,281
Latest member
tmoreira001

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top