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]

Just change E1 to K1

 

[max1995]

Hi All,

I Will try to attach the file in question.

It's pretty much the same as the first request,all in the same workbook.
I Frequently create new sheets within the same workbook , the first sheet (master) is the blank template
I Copy this template , create a new sheet within the same workbook and paste this template multiple times (ranges from 20-30) times and some times even more

The idea here is that I Want to automate this process so once I Create the new sheet and enter the number of times in a specific cell I Want the template to be Pasted, It's pasted on that new sheet.

any advice would be much appreciated.

Here's the link to the excel file : Template.xlsx

BR,

 

[Fluff]

Can you please start a thread of your own. Thanks

 

[max1995]

Can you please start a thread of your own. Thanks

Done

Multiple times paste

Hi All, I Will try to attach the file in question. It's pretty much the same as the first request,all in the same workbook. I Frequently create new sheets within the same workbook , the first sheet (master) is the blank template I Copy this template , create a new sheet within the same...

www.mrexcel.com

 

[Fluff]

 

[dannel]

Hello All

I am trying to modify the below code suggested by Fluff to make the copy multiple times. The code works as provided

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

However when i modify the Code to make it dynamic to copy based on selected range and the number of times it copies based on a input box value. It only copies once instead of number of times passed via input box. Not able to make this work (Excel attached) - selection range is A2 to D4
Please help.

   Sub Data_copyXtimes()
     
   Dim sht As Worksheet
   Dim LastRow As Long
   Dim Count As Integer
    
   Set sht = ActiveSheet
   LastRow = sht.Cells(sht.Rows.Count, "A").End(xlUp).Row
     
     If Selection.Count = 1 Then
        MsgBox ("Please select Valid Range to Copy and Paste")
     Exit Sub
     Else
        Count = InputBox("Enter no of Dupli.. in numbers", "First Select Range", 5)
        Selection.Copy Range("A" & LastRow).Offset(1, 0).Resize(LastRow * Count)
     End If
           
   End Sub

 

[dannel]

Sorry. .... missed a post submitted by Fluff on a later page.. which answers my exact below question.

I am not able to delete this question. so updating this reply. Thank you so much.

Hello All

I am trying to modify the below code suggested by Fluff to make the copy multiple times. The code works as provided

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

However when i modify the Code to make it dynamic to copy based on selected range and the number of times it copies based on a input box value. It only copies once instead of number of times passed via input box. Not able to make this work (Excel attached) - selection range is A2 to D4
Please help.

   Sub Data_copyXtimes()
    
   Dim sht As Worksheet
   Dim LastRow As Long
   Dim Count As Integer
   
   Set sht = ActiveSheet
   LastRow = sht.Cells(sht.Rows.Count, "A").End(xlUp).Row
    
     If Selection.Count = 1 Then
        MsgBox ("Please select Valid Range to Copy and Paste")
     Exit Sub
     Else
        Count = InputBox("Enter no of Dupli.. in numbers", "First Select Range", 5)
        Selection.Copy Range("A" & LastRow).Offset(1, 0).Resize(LastRow * Count)
     End If
          
   End Sub

 

[Cooksey19]

Change it to

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?

Change it to

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?

This code is so close to fitting my application perfectly. Could you please help me with a small adaptation? I have it working so that it copies and duplicates my selection where it currently is. But i want to change it so it puts these duplicates in an output table. So instead of just below the selection, i want it to start in A6 (lets say I made 3 copies), but then if i do another selection and duplicate, to paste below those (so the next duplicate would start in A9). Thank you in advance if you can help me with this. Currently, i have a macro to copy my selection and paste it where i want to, but just have to click a button x number of times. I'd love to automate this, but i just haven't learned VBA enough yet, hopefully soon i'll have the time to learn.

 

[Fluff]

Please start a thread of your own. Thank you.

 

[Cozy99]

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

Hi Fluff, Helped a lot!

However, I would like to resize each cell independent. So that A1 is pasted 3 times, A2 5 times, etc.
Thus, my problem is to copy range A1:A30 according to the values in B1:B30, whereas B1 states how many times A1 should be pasted, and so on.