Copying and pasting a range of cells n times.

[Calitri]

hI ALL,

I NEED A MACRO that copies a range of cells say from c1 to c3 six times in Column F.
say if the value in the range C1 To C3 is a b c.The range F1:F18 should have the value a b c a b c a b c a b c a b c a b c.

Please reply..urgent and advanced thanks...!

 

[VoG]

Try

Sub copy6()
Dim i As Long
Range("C1:C3").Copy
For i = 1 To 6
    Range("F" & 3 * i - 2).PasteSpecial Paste:=xlPasteValues
Next i
End Sub

 

[Peter_SSs]

Should be able to do all the multiples at once. Depending on whether you can do with Paste or need PasteSpecial ..

<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> Copy_N_a()<br>    <SPAN style="color:#00007F">Const</SPAN> myMult <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN> = 6 <SPAN style="color:#007F00">'<-Change to suit</SPAN><br>    <br>    <SPAN style="color:#00007F">With</SPAN> Range("C1:C3")<br>        .Copy Destination:=Range("F1").Resize(.Rows.Count * myMult)<br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br><br><SPAN style="color:#00007F">Sub</SPAN> Copy_N_b()<br>    <SPAN style="color:#00007F">Const</SPAN> myMult <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN> = 6 <SPAN style="color:#007F00">'<-Change to suit</SPAN><br>    <br>    <SPAN style="color:#00007F">With</SPAN> Range("C1:C3")<br>        .Copy<br>        Range("F1").Resize(.Rows.Count * myMult).PasteSpecial Paste:=xlPasteValues<br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>    Application.CutCopyMode = <SPAN style="color:#00007F">False</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

 

[entryexitexcel]

Hi,
Is there a way to insert a input box in the place of Const myMult As Long = 6 '<-Change to suit ??
I want the user to enter the number of times they want the data to be copy pasted. One time it can be 6, other time it can be 60.

Thanks,