Sub testme()

Dim wks As Worksheet
Dim wks2 As Worksheet
Dim FirstRow As Long
Dim LastRow As Long
Dim iRow As Long
Dim iNext As Long
Dim howMany As Long

Set wks = Worksheets("sheet1")
Set wks2 = Worksheets("sheet2")

wks2.Cells.ClearContents
iNext = 1
With wks
FirstRow = 2 'headers in row 1???
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row

For iRow = FirstRow To LastRow
howMany = .Cells(iRow, "B").Value
wks2.Cells(iNext, "A").Resize(howMany).Value _
= .Cells(iRow, "A").Value
With wks2.Cells(iNext, "B").Resize(howMany)
.Formula = "=row()-" & iNext - 1
.Value = .Value
End With
iNext = iNext + howMany
Next iRow
End With

End Sub



--

HTH

RP
(remove nothere from the email address if mailing direct)


"Melissa" wrote in message
...
I realised I should've added in a header row. So that's now solved.
Add-on question: can I target the results to go to another worksheet
instead
of overriding my original data?

"Melissa" wrote:

I tested the code on this data:
Adam 5
Brian 4
Carl 3

in worksheet 1, A1:B3.

Then I copied the code as per what you provided into a new macro. The
result I got was:
Adam 5
Brian 1
Brian 2
Brian 3
Brian 4
Carl 1
Carl 2
Carl 3

Brian and Carl are working fine, i.e. 4 rows for B and 3 for C. But
Adam
remains as just a single row with the value "5" next to it. What am I
doing
wrong?