Previous Topic Next Topic

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
Related:

1 reply

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