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]

No need to loop, you can do it like

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

 

[blueice2627]

wow... started typing a huge response trying to figure out why it wasn't working, just to realize i can't count... it was 13 not 14 hehe... Once i did that, magic.

Thanks a bunch for the help ! Saved me a ton of time in making the coding way to complicated!

 

[Fluff]

Glad to help & thanks for the feedback

 

[Shadkng]

Hi Fluff,

I just came upon your post #2 about copying the range down sequentially. It is exactly what I needed for my business and wanted to thank you. I can't believe it's done in one line of code!

 

[Fluff]

Glad it helped & thanks for the feedback

 

[Shadkng]

Hi Fluff, Can you explain the code- I understand that the range gets copied and then pasted into A15, and then again 14 lines down? What makes it repeat again and again without some kind of loop? I assume it's the "resize" function but I dont understand how it does it. It works so perfectly. Thankse

 

[lezawang]

I was wondering how myself. Would appreciate any break down. Thank you

Hi Fluff, Can you explain the code- I understand that the range gets copied and then pasted into A15, and then again 14 lines down? What makes it repeat again and again without some kind of loop? I assume it's the "resize" function but I dont understand how it does it. It works so perfectly. Thankse

 

[Fluff]

You resize the copy to range to the same size as the range copied (in this case 14 rows) & then multiply that by the number of copies you want.

 

[Shadkng]

Ok but how does it know to position the 3rd range at A30 and then A45 and so on.