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

Vba to copy and paste a range multiple times based on cell value
Vba copy paste range multiple times - Best answers
Copy paste range vba - Best answers
Excel VBA copy and paste loop with logic condition ✓ - Forum - Excel
Copy row and insert n times ✓ - Forum - Office Software
Macro to copy/paste down X times (where X is the number of rows) ✓ - Forum - Excel
VBA - Copy range # of times value is in Colum - Forum - Excel
VBA code to copy and paste data multiple times - Forum - Excel

1 reply

Answer 1 / 1

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

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.

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.

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

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.

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!

Subscribe To Our Newsletter!