Generate list based on number

[Carla Bradley]

I hope I can explain this well, but before I do this is
Win and Excel XP.

I have a client that has a workbook. Each sheet in the
workbook is a company location. Under each sheet is a
list of people and a number among other things. These
numbers indicate how many chances a person is eligible
for in a drawing. She wants to create an additional sheet
with the correct number of names for each sheet all on
this last sheet. For example she has sheet1 through
sheet8 and on sheet1 it says John Doe 30 and on sheet2 is
says Suzi Q 20, she wants to create sheet9 that has john
doe's name 30 times and suzi q's name 20 times etc... Is
this possible?

Thanks,
Carla Bradley
(e-mail address removed)

 

[Myrna Larson]

This would be cumbersome to do with formulas, fairly easily with VBA. Here's
some code. Go to the VB Editor (ALT+F11), select your workbook over in the
left hand project pane, then insert a module (insert menu) and paste the code
below into the code pane you see on the right.

I assumed the name is in A2 and the number in B2. That most likely isn't
correct, so you'll have to modify those 2 lines in the code to correspond with
your layout. The lines are marked with <<<<<

To generate the list, it's Tools/Macro/Macros, select ListNames from the
dropdown and click Run.

Option Explicit

Sub ListNames()
Dim DestRow As Long
Dim ListSheet As Worksheet
Dim LSX As Long
Dim Num As Long
Dim Sh As Long
Dim TheName As String

'assume the name is in A2, the number is in B2
Const NameAddr As String = "A2" '<<<<<
Const NumAddr As String = "B2" '<<<<<

Set ListSheet = Worksheets.Add(After:=Worksheets(Worksheets.Count))
ListSheet.Name = "Final List"
LSX = ListSheet.Index
DestRow = 2

For Sh = 1 To ThisWorkbook.Worksheets.Count
If Sh <> LSX Then
With Worksheets(Sh)
TheName = .Range(NameAddr).Value
Num = .Range(NumAddr).Value
End With
If TheName <> "" And Num > 0 Then
ListSheet.Cells(DestRow, 1).Resize(Num, 1).Value = TheName
DestRow = DestRow + Num
End If
End If
Next Sh
End Sub