Need Macro- Copy Row and Insert 10 times [Solved/Closed]

Report
Posts
6
Registration date
Monday October 28, 2013
Status
Member
Last seen
October 31, 2013
-
Posts
6
Registration date
Monday October 28, 2013
Status
Member
Last seen
October 31, 2013
-
Hello,

I have a similar question as this previous thread: https://ccm.net/forum/affich-107673-copy-row-and-insert-n-times but I'm not sure which code to use.

Sample
ColA ColB ColC
TextA TextA1 TextA2
TextB TextB1 TextB2
TextC TextC1 TextC2

Results (for example 3 times)
ColA ColB ColC
TextA TextA1 TextA2
TextA TextA1 TextA2
TextA TextA1 TextA2
TextB TextB1 TextB2
TextB TextB1 TextB2
TextB TextB1 TextB2
TextC TextC1 TextC2
TextC TextC1 TextC2
TextC TextC1 TextC2

Help would be greatly appreciated from this newbie!

THanks!

2 replies

Posts
1862
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
July 30, 2015
791
data is like this in sheet1 from A1

hdng1 hdng2 hdng3
TextA TextA1 TextA2
TextB TextB1 TextB2
TextC TextC1 TextC2


now try this macro

in the macro there is a statement
j=10
that is the rows in sheet1 will be copied 10 times. if you want different number modifgy this statement

result in sheet 3

Sub test()
Dim j As Integer, r As Range, c As Range, r1 As Range, r2 As Range
j = 10
Application.ScreenUpdating = False
Worksheets("sheet3").Cells.Clear
Set r = Range(Range("A2"), Range("A2").End(xlDown))
For Each c In r
Range(c, c.End(xlToRight)).Copy

With Worksheets("sheet3")
Set r1 = .Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)
.Range(r1, r1.Offset(j - 1, 0)).PasteSpecial
End With
Next c
Application.CutCopyMode = False
Application.ScreenUpdating = True
End Sub
Posts
6
Registration date
Monday October 28, 2013
Status
Member
Last seen
October 31, 2013

Hi Venkat1926,

It worked first try! Thank you so much for your help!