VBA Copy/Paste fixed range a number of times [Solved]

Posts
4
Registration date
Thursday September 19, 2019
Status
Member
Last seen
October 10, 2019
-
Hello Team,


I'm looking for a VBA code to copy the three rows and with input box paste based on the times, I entered in the input box.

FOr example:
I want to copy rows(A2 to A6) and paste all the rows from A7 and so on.. The header(A1) should not be copied.

I need to copy all the formulas in the rows(A2 to A6)

Please help
See more 

1 reply

Best answer
Posts
2576
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
December 10, 2019
383
1
Thank you
Hi Srinivas,

Give the following code a try:
Sub RunMe()
Dim x, dRow, Counter As Integer

Counter = InputBox("How many times would you like to paste?:")
dRow = 7

Range("A2:A6").Copy

For x = 1 To Counter
    Range("A" & dRow).PasteSpecial
    dRow = dRow + 5
Next x

Application.CutCopyMode = False
End Sub


Best regards,
Trowa

Say "Thank you" 1

Glad we were able to help! Love us? Write us a review! Rate CCM

CCM 5780 users have said thank you to us this month

srinivas_kala
Posts
4
Registration date
Thursday September 19, 2019
Status
Member
Last seen
October 10, 2019
-
Hi Trowa,

Thanks for the Help. Works like a champ.
srinivas_kala
Posts
4
Registration date
Thursday September 19, 2019
Status
Member
Last seen
October 10, 2019
-
Hi Trowa,

I need to stop the code after 200 rows(40 times to copy A1:A5). Please help.
TrowaD
Posts
2576
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
December 10, 2019
383 -
Hi Srinivas,

For that a few minor changes are needed:

Sub RunMe()
Dim x, dRow, Counter As Integer

Counter = 39
dRow = 6

Range("A1:A5").Copy

For x = 1 To Counter
    Range("A" & dRow).PasteSpecial
    dRow = dRow + 5
Next x

Application.CutCopyMode = False
End Sub


Best regards,
Trowa
srinivas_kala
Posts
4
Registration date
Thursday September 19, 2019
Status
Member
Last seen
October 10, 2019
-
Hi Trowa,

Sorry for the delayed response. I was on field visit. Thank you very much. this is what I really need. Once again thank you for your time and help.
TrowaD
Posts
2576
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
December 10, 2019
383 -
Nice, thanks for the feedback!
Respond to TrowaD