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

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

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

1 reply

Answer 1 / 1
Posts
2717
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
April 1, 2021
 458
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

1
Thank you
Thank you

A few words of thanks would be greatly appreciated. Add comment

CCM 2942 users have said thank you to us this month

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
2717
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
April 1, 2021
 458
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
2717
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
April 1, 2021
 458
Nice, thanks for the feedback!
Ask a question

Subscribe To Our Newsletter!

The Best of CCM in Your Inbox

Subscribe To Our Newsletter!