VBA Copy/Paste fixed range a number of times

Solved/Closed
srinivas_kala Posts 4 Registration date Thursday September 19, 2019 Status Member Last seen October 10, 2019 - Updated on Sep 24, 2019 at 11:55 AM
TrowaD Posts 2900 Registration date Sunday September 12, 2010 Status Moderator Last seen September 12, 2022 - Oct 15, 2019 at 11:41 AM
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

TrowaD Posts 2900 Registration date Sunday September 12, 2010 Status Moderator Last seen September 12, 2022 523
Sep 23, 2019 at 12:07 PM
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
srinivas_kala Posts 4 Registration date Thursday September 19, 2019 Status Member Last seen October 10, 2019
Sep 27, 2019 at 11:51 AM
Hi Trowa,

Thanks for the Help. Works like a champ.
0
srinivas_kala Posts 4 Registration date Thursday September 19, 2019 Status Member Last seen October 10, 2019
Sep 27, 2019 at 01:01 PM
Hi Trowa,

I need to stop the code after 200 rows(40 times to copy A1:A5). Please help.
0
TrowaD Posts 2900 Registration date Sunday September 12, 2010 Status Moderator Last seen September 12, 2022 523
Sep 30, 2019 at 11:48 AM
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
srinivas_kala Posts 4 Registration date Thursday September 19, 2019 Status Member Last seen October 10, 2019
Oct 10, 2019 at 06:27 AM
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
TrowaD Posts 2900 Registration date Sunday September 12, 2010 Status Moderator Last seen September 12, 2022 523
Oct 15, 2019 at 11:41 AM
Nice, thanks for the feedback!
0