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:

[TABLE="class: grid, width: 200, align: left"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]John[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Jane[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Steve[/TD]
[TD]3[/TD]
[/TR]
</tbody>[/TABLE]







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

[TABLE="class: grid, width: 200, align: left"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]John[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]John[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Jane[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Jane[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]Jane[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]Jane[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]Jane[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]Steve[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]Steve[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]Steve[/TD]
[/TR]
</tbody>[/TABLE]

















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