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 January 16, 2023 - 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
If cell contains text then return value multiple conditions ✓ - Excel Forum
Compare two worksheets and paste differences to another sheet - excel vba free download ✓ - Excel Forum
You've tried to log in too many times. to protect your account, a temporary block has been placed on logging in. please try again later. ✓ - Facebook Forum
Based on the values in cells b77:b81, what function can automatically return the value in cell c77? ✓ - Excel Forum
How to change account based in instagram - Instagram Forum

1 response

Answer 1 / 1

TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen January 16, 2023 549
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 January 16, 2023 549
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 January 16, 2023 549
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

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

Code to move rows into another sheet based on certain values

Inserting image in a particular cell in VBA

Similar discussions

Subscribe To Our Newsletter!