Copy a range of cells and paste X times based on # variable

[jh]

Greetings,


Haven't been able to quite figure out how to do this, probably VBA required.


I have data that is on Sheet2 that covers A1:G5, based on a # variable either linked to a cell or a pop-up box that would appear, It would copy this range X times on the same sheet with 1 row separations. (Ex: If I wanted only 1, it would copy A1:G5 and paste one set covering A7:G12. Row 6 would be empty)


The reason for the need of a macro/VBA/formula is there might be 60+ and trying to save time having to manually copy and paste.


Thanks everyone and look forward to your reply.


Signed,

JH

 

[Luke M]

The math operation then is going to be

DestinationRow = 1 + 6 * i

where i goes from 1 to x

Full code:

[pre]

Sub CopyData()
Dim MyRange As Range
Dim i As Integer
Dim xInput As Integer

Set MyRange = Range("A1:G5")
On Error Resume Next
Do
xInput = InputBox("How many times would you like to copy data?", "Copy Count", 1)
Loop Until xInput >= 0
On Error GoTo 0
If xInput = 0 Then Exit Sub ' User aborted

Application.ScreenUpdating = False
For i = 1 To xInput
MyRange.Copy Cells(1 + 6 * i, "A")
Next i
Application.ScreenUpdating = True
End Sub

[/pre]

 

[jh]

Spot on, amazing. Thanks Luke!


Signed,

JH

 

[Luke M]

Glad I could help. =)