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 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 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

Insert a new sheet at the end of the tab names and paste the range names starting in cell a1. autofit columns a:b and name the worksheet as range names.
Excel Macro - Set and autofill a range? ✓ - Excel Forum
Ideogram ai names - Guide
My contacts list names - Guide
Wow monk names - Guide
Laptop keyboard symbol names - Guide

1 response

Answer 1 / 1

TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 555
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 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 555
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 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 555
Oct 15, 2019 at 11:41 AM

Nice, thanks for the feedback!

Similar discussions

VBA code to copy and paste data multiple times

Excel VBA copy and paste loop with logic condition

VBA - Copy range # of times value is in Colum

How to duplicate rows x number of times

VBA Code to Copy Worksheet multiple times and rename based on list

Macro to insert row based on variable cell value (text)

Similar discussions

Subscribe To Our Newsletter!