Menu
Ask a question Report

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
 -
Excel Ask a question Report
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
Report Respond to TrowaD
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

Report Respond to srinivas_kala
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.
Report Respond to srinivas_kala
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.
Report Respond to TrowaD
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
Report Respond to srinivas_kala
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.
Report Respond to TrowaD
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
Reply Ask a question