VBA to copy and paste a range multiple times based on cell value

blueice2627

New Member
Joined
May 21, 2018
Messages
10
Hi All,

I'm trying to create a macro that will take a range from one worksheet (total of 4 columns and 14 rows), and copy it multiple times below each paste (one after another) based on the value in another cell. Essentially I am using this macro to create coupons and based on the number of coupons, it will continue to copy and paste until the correct number of coupons have been made. This is my first time doing what i think needs to be a loop statement, any help would be greatly appreciated!

What I have created so far is:
Code:
    Dim CopyCount As Integer
    Dim Rng As Range
    Dim SecondSetStart As Range
    
    CopyCount = RepeatTimes  'This refers to the cell that has the number of times to repeat the copy/paste 
    For x = 1 To CopyCount
    Set Rng = Range("SecondSet") 'SecondSet is the range of cells that is being copied
    Set StartRng = Range("A14") 'StartRng is where the first paste will go, following pastes need to be 13 rows after this, A27, A40, etc...
    
    Do Until x = CopyCount
    
    Range("Rng").Select
    Selection.Copy
    Sheets("Coupon Printing").Select
    Range("StartRng").Select
    ActiveSheet.Paste

Where i am getting lost... or the end of my knowledge on this is subject... How do i make my range a variable that increments 13 rows to continue the loop?

Thanks again in advance!
 
Hi & welcome to MrExcel.
If you want to copy across the columns you need to do it like
Code:
Sub Brinny()
Range("A1:D14").Copy Range("A15").Resize(, 14 * Range("E1").Value)
End Sub
 
Upvote 0

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)
You're welcome & thanks for the feedback
 
Upvote 0
No need to loop, you can do it like
Code:
Sub CopyMulti()
Range("A1:D14").Copy Range("A15").Resize(14 * [COLOR=#ff0000]Range("E1").Value[/COLOR])
End Sub
Where the range in red contains the number of copies you need

Incredibly useful! Two follow up questions:

1. I've tried various ways to ESC when running a 200x copy but nothing works and its not until I force close the excel that the debugger shows up for a second before closing with the excel. Is there a way to ESC? I've tried FN+B, CTRL+Pause/Break, and ESC.

2. Is there a way to edit this to use my current range selection as the input, and copy directly below it multiple times?

Thanks so much!
 
Upvote 0
The code you have quoted takes less than 1/10th of a second even when E1 is 5000.

T0 use a selected range try
Code:
Sub CopyMulti()
With Selection
   .Copy Range("A15").Resize(.Rows.Count * Range("E1").Value)
End With
End Sub
 
Upvote 0
I am applying the code to a massive range so it takes about 20 minutes, and sometimes I want to be stop it before it’s done.


Here is the code I am using with your latest updates:

Code:
Sub CopyMultiSelection()
Application.Calculation = xlManual
Count = InputBox("How many times to copy?")
With Selection
   .Copy Range("A128").Resize(.Rows.Count * Count)
End With
End Sub

Regarding having it paste down from the bottom of the selection, what should I change the Range("A128") to have it do so, whereever the bottom of the selection may be?

Thanks!
 
Upvote 0
Change it to
Code:
Sub CopyMultiSelection()
Application.Calculation = xlManual
Count = InputBox("How many times to copy?")
With Selection
   .Copy .Offset(.Rows.Count).Resize(.Rows.Count * Count)
End With
End Sub
But with 2500 rows & copying 400 times takes only 3 or 4 seconds.
You obviously have something else going on.
Do you have any event code?
 
Upvote 0
This is the size of the range "A90:VV127" and I am trying to copy it 200 times, each cell has many many nested formulas. I don't have any other macros but the workbook itself is around 60mb and gets to 120mb when everything is copied.
 
Upvote 0
That range isn't that big & should still only take a few seconds with calculation turned off.
If it takes you 20minutes, there is something else going on.
As the code is effectively only 1 line there is no way of interrupting it.
 
Upvote 0
No need to loop, you can do it like
Code:
Sub CopyMulti()
Range("A1:D14").Copy Range("A15").Resize(14 * [COLOR=#ff0000]Range("E1").Value[/COLOR])
End Sub
Where the range in red contains the number of copies you need

Is there a way for me to set it to copy x time set in a certain cell, for ex: K1?
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,213
Members
448,554
Latest member
Gleisner2

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
Back
Top