Copy & Paste a range of data 100 times

[VinodKrishnappa]

Please can any one help me on this...
I want to copy paste a range of data (A1: F20) one after one 100 times in the same sheet after giving a row gap for each paste value, i need some help in the below macros

Sub Macro3()
'
' Macro3 Macro
' Copy&OPaste
'
' Keyboard Shortcut: Ctrl+z
'
Selection.Copy
Range("A8").Select
ActiveSheet.Paste
Range("A15").Select
ActiveSheet.Paste
Range("A22").Select
ActiveSheet.Paste
ActiveWindow.SmallScroll Down:=100
End Sub

 

[MickG]

Try this:-
This assumes row 21 is blank

[COLOR="Navy"]Sub[/COLOR] MG30Aug23
[COLOR="Navy"]Dim[/COLOR] ray [COLOR="Navy"]As[/COLOR] Variant, n [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
ray = Range("A1:F21")
[COLOR="Navy"]For[/COLOR] n = 22 To 2100 [COLOR="Navy"]Step[/COLOR] 21
    Cells(n, 1).Resize(21, 6).Value = ray
[COLOR="Navy"]Next[/COLOR] n
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[Peter_SSs]

Your description (A1:F20) and your code sample (A8, A15, A22) don't seem to agree.

If Mick's suggestion (or the following non-looping similar code) does not do what you want, then you may need to clarify further.

Sub CopyMultiple()
  With Range("A1:F21")
    .Copy Destination:=.Resize(.Rows.Count * 100)
  End With
End Sub

 

[My Aswer Is This]

Try this:
Copying in range below Range("A1:F20")

Sub Copy_Me()
Application.ScreenUpdating = False
Dim i As Long
Dim Lastrow As Long
Lastrow = Cells(Rows.Count, "A").End(xlUp).Row + 2
    For i = 1 To 100
        Range("A1: F20").Copy Destination:=Range("A" & Lastrow)
        Lastrow = Cells(Rows.Count, "A").End(xlUp).Row + 2
    Next
Application.ScreenUpdating = True
End Sub

 

[My Aswer Is This]

That is a interesting way to do it. I need to remember this.

Your description (A1:F20) and your code sample (A8, A15, A22) don't seem to agree.

If Mick's suggestion (or the following non-looping similar code) does not do what you want, then you may need to clarify further.

Sub CopyMultiple()
  With Range("A1:F21")
    .Copy Destination:=.Resize(.Rows.Count * 100)
  End With
End Sub

 

[Peter_SSs]

Try this:

This code results in 101 blocks of the data altogether, whereas Mick's and mine result in 100 blocks altogether. Don't know which the OP wants, just noting the difference.

 

[oldbrewer]

1	2	3	4					R1 is the number of rows in original block		4		7		3
5	6	7	8					N1 is number of columns in the block
9	10	11	12					P1 is how many duplicate blocks required
																3
1	2	3	4
5	6	7	8
9	10	11	12
																7
1	2	3	4
5	6	7	8
9	10	11	12
								this macro will automatically detect how many initial rows and columns		11
1	2	3	4					and how many duplicate blocks required
5	6	7	8
9	10	11	12					rrow = Cells(1, 18) + 2
								For j = 1 To Cells(1, 16)						15
1	2	3	4					For k = 1 To Cells(1, 18)
5	6	7	8					For L = 1 To Cells(1, 14)
9	10	11	12					Cells(rrow, L) = Cells(k, L)
								Next L								19
1	2	3	4					rrow = rrow + 1
5	6	7	8					Next k
9	10	11	12					rrow = rrow + 1
								Next j
1	2	3	4					End Sub
5	6	7	8
9	10	11	12
1	2	3	4
5	6	7	8
9	10	11	12

<colgroup><col span="19"></colgroup><tbody>
</tbody>