Thread: Copy a cell to another sheet N number of times Excel 2007

Try this:

Sub CopyMultiples()
Dim MyRng As Range, Num As Range, NR As Long

Set MyRng = Sheets("Sheet1").Range("B:B").SpecialCells(xlConstants, xlNumbers)

With Sheets("Sheet2")
    NR = .Range("A" & .Rows.Count).End(xlUp).Row + 1
    
    For Each Num In MyRng
        .Range("A" & NR).Resize(Num).Value = Num.Offset(, -1).Value
        NR = NR + Num
    Next Num
End With
    
End Sub

hi kowshick, option, press Start

Sub test()
Dim data, result, i As Long, n As Long, j As Long

If Range("a1") = "" Then Exit Sub

With Range("a1", Cells(Rows.Count, "a").End(xlUp))
    data = .Resize(, 2)
    ReDim result(1 To Application.Sum(.Offset(, 1)), 1 To 1)
End With

For i = 1 To UBound(data)
    If data(i, 1) <> "" Then
        For n = 1 To data(i, 2)
            j = j + 1
            result(j, 1) = data(i, 1)
        Next
    End If
Next

If j > 0 Then Sheets.Add(after:=Sheets(Sheets.Count)).Range("a1").Resize(j) = result

End Sub

If that takes care of your need, please click EDIT in your original post, click GO ADVANCED and set the PREFIX box to SOLVED.