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

[blueice2627]

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:

    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!

 

[Fluff]

Because it's copying 14 rows.

 

[Shadkng]

Hi, would it be possible to add to your code below to copy the row height from the original range to the new range? Thanks

Sub CopyMulti()
Range("A1:D14").Copy Range("A15").Resize(14 * [COLOR=#ff0000]Range("E1").Value[/COLOR])
End Sub

 

[Fluff]

Are the row heights all the same?

 

[Shadkng]

Yes they are

 

[Fluff]

In that case use

Sub CopyMulti()
Range("A1:D14").Copy Range("A15").Resize(14 * Range("E1").Value)
Range("A15").Resize(14 * Range("E1").Value).RowHeight = Range("A1").RowHeight
End Sub

 

[Shadkng]

Thank you works great!

 

[Fluff]

You're welcome & thanks for the feedback

 

[adamsh]

Hey, good morning. This thread has been massively useful for me so far - thanks! However I have the situation where the row heights in the original range are not all the same but need to be retained. Would you please be able to advise on how to achieve this?

 

[Fluff]

Hi & welcome to MrExcel.
You will need to loop through the rows, setting the rowheight one by one.

 

[brinny]

Hi Fluff,

I'm very new to VBA and this thread was also super helpful for me. I used your single line of code to copy a chunk of data and paste it below the original chunk repeatedly based on a number in a cell. However, when I try to use that code to paste the chunk of data to the left of the original chunk repeatedly based on the number in the cell, it only pastes it once instead of the number of times indicated. Is there a way to resolve this? Why does it only work pasting downward instead of to the left? Thanks in advance!