Menu
  1. Accueil
  2. Tech Forum
  3. Office Software
  4. Excel

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

Report
Posts
4
Registration date
Thursday September 19, 2019
Status
Member
Last seen
October 10, 2019
 -
Posts
2638
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
August 4, 2020
 -
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

Answer 1 / 1
Posts
2638
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
August 4, 2020
 432
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

Report
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
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
Posts
2638
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
August 4, 2020
 432
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
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
Posts
2638
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
August 4, 2020
 432
Nice, thanks for the feedback!
Reply Ask a question