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
2847
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
January 13, 2022
-
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

Posts
2847
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
January 13, 2022
491
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
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.
0
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.
0
Posts
2847
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
January 13, 2022
491
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
0
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.
0
Posts
2847
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
January 13, 2022
491
Nice, thanks for the feedback!
0