Previous Topic Next Topic

VBA Copy/Paste fixed range a number of times

Solved/Closed
srinivas_kala Posts 4 Registration date Thursday 19 September 2019 Status Member Last seen 10 October 2019 - 19 Sep 2019 à 12:31
TrowaD Posts 2921 Registration date Sunday 12 September 2010 Status Contributor Last seen 27 December 2022 - 15 Oct 2019 à 11:41
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

Réponse 1 / 1
TrowaD Posts 2921 Registration date Sunday 12 September 2010 Status Contributor Last seen 27 December 2022 555
23 Sep 2019 à 12:07
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 19 September 2019 Status Member Last seen 10 October 2019
27 Sep 2019 à 11:51
Hi Trowa,

Thanks for the Help. Works like a champ.
srinivas_kala Posts 4 Registration date Thursday 19 September 2019 Status Member Last seen 10 October 2019
27 Sep 2019 à 13:01
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 12 September 2010 Status Contributor Last seen 27 December 2022 555
30 Sep 2019 à 11:48
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 19 September 2019 Status Member Last seen 10 October 2019
10 Oct 2019 à 06:27
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 12 September 2010 Status Contributor Last seen 27 December 2022 555
15 Oct 2019 à 11:41
Nice, thanks for the feedback!

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