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 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
Thanks to venkat1926 for this tip on the forum.