Excel - Copy rows from sheet 1 to sheet 2


I need to have a macro that will copy rows from sheet1 to sheet 2 . The catch is from sheet 1 col A for eg A3 may have the number 10 in it. I need to copy that row and paste it 10 times on sheet 2 and then copy the next row from Sheet 1 A4 and if it has 2 in that column paste it 2 times on Sheet 2 and so on.


I presume you have data (numbers ) only from A3 down
see the sample data below (this is in sheet1)

A3 is 10
A4 is 2

now try this macro and see sheet 2

Sub test() 

Dim r As Range, c As Range, dest As Range
Dim j As Long, r1 As Range
Set r = Range(Range("A3"), Range("A3").End(xlDown))
For Each c In r
j = c.Value
With Worksheets("sheet2")
Set dest = .Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)
Set r1 = Range(dest, dest.Offset(j - 1, 0))
End With
Next c
End Sub


Thanks to venkat1926 for this tip on the forum.
Published by aakai1056. Latest update on April 2, 2010 at 08:55 AM by aakai1056.
This document, titled "Excel - Copy rows from sheet 1 to sheet 2," is available under the Creative Commons license. Any copy, reuse, or modification of the content should be sufficiently credited to CCM (https://ccm.net/).
Excel - How to merge rows of similar data?
Excel - Count 2 values in one column and 1 value in other