creating new cells based on cell value in separate sheet

[licorice1969]

Good morning ... I'm not sure if this can be done, or even how exactly to describe it, but here goes ...

I need to create a "ballot" file, which I will then refer to after generating a random number using a formula.

I have a source spreadsheet with names and the # of ballots each name gets, what I don't know how to do is to generate a separate worksheet or workbook, which will then put each name in a cell in column A the appropriate number of times.

For example, if the source workbook is:

	A	B
1	John	2
2	Jane	5
3	Steve	3

<tbody>
</tbody>

... the new worksheet/workbook would look like this:

	A
1	John
2	John
3	Jane
4	Jane
5	Jane
6	Jane
7	Jane
8	Steve
9	Steve
10	Steve

<tbody>
</tbody>

Does anyone know how to accomplish this, or even point me in the right direction?

Much thanks in advance for any assistance!

PS - Not sure why there are so many blank lines after the 2nd table, when I edit the post there are none.

 

[Fluff]

Hi & welcome to MrExcel.
How about

Sub licorice1969()
   Dim Cl As Range
   Dim Ws As Worksheet
   
   Set Ws = Sheets.Add
   With Sheets("[COLOR=#ff0000]Master[/COLOR]")
      For Each Cl In .Range("A2", .Range("A" & Rows.Count).End(xlUp))
         Cl.Copy Ws.Range("A" & Rows.Count).End(xlUp).Offset(1).Resize(1 * Cl.Offset(, 1).Value)
      Next Cl
   End With
End Sub

Change sheet name to suit

 

[licorice1969]

Yes, that did the trick - thanks!

 

[Fluff]

You're welcome & thanks for the feedback