Excel - Copy rows from sheet 1 to sheet 2

March 2017




Issue


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.

Solution


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
Worksheets("sheet1").Activate
Set r = Range(Range("A3"), Range("A3").End(xlDown))
For Each c In r
j = c.Value
c.EntireRow.Copy
With Worksheets("sheet2")
Set dest = .Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)
Set r1 = Range(dest, dest.Offset(j - 1, 0))
r1.PasteSpecial
End With
Next c
application.cutcopymode=false
End Sub

Note


Thanks to venkat1926 for this tip on the forum.

Related


Published 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 (ccm.net).