Previous Topic Next Topic

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

1 response

Answer 1 / 1
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 552
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

1
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.
0
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.
0
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 552
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
0
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.
0
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 552
Oct 15, 2019 at 11:41 AM
Nice, thanks for the feedback!
0

Similar discussions

VBA code to copy and paste data multiple times
lakshminair -
ac3mark -

1 response
Excel VBA copy and paste loop with logic condition
Yogibearv -
Yogibearv -

6 responses
VBA - Copy range # of times value is in Colum
becca -
rizvisa1 -

3 responses
How to duplicate rows x number of times
carlos.guerrero18 -
ac3mark -

4 responses
VBA Code to Copy Worksheet multiple times and rename based on list
Hpence -
rogerio -

3 responses
Macro to insert row based on variable cell value (text)
deadxcell -
vcoolio -

1 response