How to copy a range n times

[Waimea]

I am trying to copy a range ("A2:H41") 40 times down but I would also like the choice to copy it a range x times down.

Sub CopyMeTimesDown
   
    Dim rng As range
    Set rng = range("A2:H41")
    Dim batchSize As Integer
    Set batchSize = 40
    Dim x As Integer
        
    rng.Select
    For x = 1 To 1601 Step batchSize
         ActiveSheet.Paste
    Next x
    
End Sub

I can't get this to work, what am I doing wrong?

 

[Fluff]

Try

Dim Qty As Variant
Qty = InputBox("Please enter a number")
If Qty <> "" Then Range("A2:H41").Copy Range("A42").Resize(40 * Qty, 8)

 

[Waimea]

Hi Fluff,

thank you for your reply! Your code does exactly what I need.

 

[Fluff]

You're welcome & thanks for the feedback

 

[Waimea]

What does 8 in the Resize(40 * Qty, 8) do?

 

[Peter h]

I am trying to copy a range ("A2:H41") 40 times down but I would also like the choice to copy it a range x times down.

Sub CopyMeTimesDown
   
    Dim rng As range
    Set rng = range("A2:H41")
    Dim batchSize As Integer
    Set batchSize = 40
    Dim x As Integer
        
    rng.Select
    For x = 1 To 1601 Step batchSize
         ActiveSheet.Paste
    Next x
    
End Sub

I can't get this to work, what am I doing wrong?

You can try this. You may have to change the Worksheet name. Also, I made it to copy the data however many times is entered into Cell "A1". You can change that or do away with that part of the code if you want.

Dim ws As Worksheet
Dim x As Integer
Dim batchSize As Integer
Dim rng As Range
Dim nmbr As Integer
Set ws = ThisWorkbook.Sheets("Sheet1")
Set rng = ws.Range("A2:H41")
batchSize = 40
nmbr = (ws.Cells(1, 1).Value + 1) * batchSize
If ws.Cells(1, 1) = "" Then
    MsgBox "Enter a number into A1.", vbOKOnly
    Exit Sub
End If
rng.Copy
For x = 2 To nmbr Step batchSize
    ws.Range("A" & x).PasteSpecial xlPasteAll
Next x

 

[Fluff]

What does 8 in the Resize(40 * Qty, 8) do?

It resizes the copy to range 8 columns to the right, ie col H

 

[Waimea]

Hi Peter,

thank you for your reply! I get a runtime error 13: type mismatch at

nmbr = (ws.Cells(1, 1).Value + 1) * batchSize

I have tried changing nmbr to variant without success

 

[MARK858]

Also, I made it to copy the data however many times is entered into Cell "A1"

Waimea, did you put the number in A1?

 

[Waimea]

No, I missed that and thought that a msgbox would ask me for a number.

However, you are correct and now it works!