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
 -
TrowaD
Posts
2596
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
March 5, 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
2596
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
March 5, 2020
 401
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

Comment
Thank you
1
Thank you

Glad we were able to help! Love us? Write us a review! Rate CCM

CCM 3644 users have said thank you to us this month

Report
srinivas_kala
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
srinivas_kala
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
TrowaD
Posts
2596
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
March 5, 2020
 401
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
srinivas_kala
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
TrowaD
Posts
2596
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
March 5, 2020
 401
Nice, thanks for the feedback!
Reply Ask a question