Sub tablemaker()
Dim s1 As Worksheet, s2 As Worksheet, r As Range
Set s1 = Sheets("Sheet1")
Set s2 = Sheets("Sheet2")
s1.Activate
Set r = ActiveSheet.UsedRange
nLastRow = r.Rows.Count + r.Row - 1

k = 1
For i = 1 To nLastRow
Label = s1.Cells(i, 1).Value
Start = s1.Cells(i, 2).Value
Count = s1.Cells(i, 3).Value
For j = 1 To Count
s2.Cells(k, 1).Value = Label
s2.Cells(k, 2).Value = Start + j - 1
k = k + 1
Next
Next
End Sub


So if Sheet1 started with:

alpha 12 4
beta 100 6
gamma 2 7

Then the macro would produce on Sheet2:

alpha 12
alpha 13
alpha 14
alpha 15
beta 100
beta 101
beta 102
beta 103
beta 104
beta 105
gamma 2
gamma 3
gamma 4
gamma 5
gamma 6
gamma 7
gamma 8

--
Gary's Student


" wrote:

Here is what I am trying to do.

I have a spreadsheet with 3 values; a text string ("ABC123"), the first
number in a counter ("100") and a number ("10"). The number is the
number of line-items I need, the text string need to be the same on
each line and the count needs to be incremented by one for each line.
So basically I am trying to come up with a macro that will have the
following output.

ABC123 100
ABC123 101
ABC123 102
ABC123 103
ABC123 104
ABC123 105
ABC123 106
ABC123 107
ABC123 108
ABC123 109

Any ideas on how this would be accomplished?