Copy and Paste Mulitple times

[Fool]

I did try earnestly to find the answer before posting as I assume this will be a question you have answered many times. Unfortunatley I just can't seem to find a senario that fits what I am trying to do.

Either that or I don't have enough VBA knowledge to understand that the answere is out there and I am just to foolish to realise.

I have a created a template in Excel and want to offer the user to copy and paste it several times down the sheet.

What I want to do is start off with one form on the sheet as a template then write a macro to copy and past the form on a new row looping for the number of times specified by the user.

So in detail.


User option - what band does the item come under?
Band 1 =paste 15 times
Band 2 =paste 20 times
Band 3 =paste 25 times.... and so on.

Get user option
If user option=1 then loop=15 (and so on)

Get user option=number of loops

Select cells range A1:D35 (location of the form)
Copy
Move to the end of the range then move down x number of rows

Paste

Loop until end user choice


I am a complete VBA novice some help would be greatley appreciated.

Thank you!

 

[Robert E Lee]

Try this

Dim rng As Range, i As Integer, n As Long
Dim R As Integer
Set rng = Range("A1", "D35")
R = 35
n = 0
i = InputBox("Enter repetitions")
        Do Until n = i + 1
            rng.Copy Destination:=Range("A" & R + 1)
            n = n + 1
            R = R * (n + 1)
        Loop

I have been a bit lazy in setting R to 35, this can be made a variable by setting R to equal last row

HTH

Robert