Easier way of adding new sheets to workbook

[SYBS]

Hello there,

I have a workbook which consists of one main page to enter Judges names and
competitors names onto, a 2nd sheet containing one template score sheet and
hidden sheets (5) At the moment, user selects 3/4/ or 5 judges, then enters
their names on rows 9 - 13 col B. Macros attached to the original selection
of 3/4/ or 5 judges produce a very long winded means of copying the One
section template and pasting either 2/3 or four more on to the end of another
hidden sheet, creating the correct number of sections on that worksheet.
When the judges names are entered on the original sheet, there are also
macros that transfers that judges name on to the relevant section of the new
worksheet.

The number of worksheets added depends on the number of competitors names on
the list. (Up to five, entered on rows 16 - 20 col B,). Each new worksheet
should be named as the competitor's (name)as listed.

I also need to be able to refer to the sheets in the workbook in a way that
references their numbers, not the Competitors name, because that will vary
for each competition, as data from these sheets gets transfered to a ranking
sheet later. If possible I would like to enterthe judges names, either 3/4
or 5 or them on the list, which would then produce the required sectioned
sheet. Then as the competitors names are entered, new sheets created from
that and named.
For for the next use of the programme, I would like to be able to go back to
the beginning without any visible sheet names from the previous use being
retained.

I have tried many different ways of doing these things and although they all
work in their own way, I am sure there must be an easier way of doing these
things. I just dont know how. Hope you can make sense of the problem. .
Any offers of help gratefully received.

Thanks

Sybs

 

[Joel]

this code will do what you need it to do. change the Const statements to
match your worksheet names. the code at the botttom will let you find the
contestants or judges worksheets. The judges worksheets start with Judge and
the competitors worksheets start with competitor.

Sub CreateSheets()

Const JudgesTemplet = "Judges Templet"
Const MainSh = "Main"
Const ScoreTemplet = "Scores Templet"

Sheets(MainSh).Activate
Set JudgesRange = Sheets(MainSh).Range("B9:B13")
Set CompetitorsRange = Sheets(MainSh).Range("B16:B20")

For Each Cell In JudgesRange


If Not IsEmpty(Cell) Then

Worksheets(JudgesTemplet).Copy After:=Worksheets(JudgesTemplet)
ActiveSheet.Name = "Judge " + Cell

End If
Next Cell

For Each Cell In CompetitorsRange


If Not IsEmpty(Cell) Then

Worksheets(ScoreTemplet).Copy After:=Worksheets(ScoreTemplet)
ActiveSheet.Name = "Competitor " + Cell

End If
Next Cell


'get competitors
For Each Mysheet In ThisWorkbook.Sheets

If StrComp(Left(Mysheet.Name, 10), "Competitor") = 0 Then

a = 1 ' this is a competitor worksheet

End If

If StrComp(Left(Mysheet.Name, 5), "Judge") = 0 Then

a = 1 ' this is a competitor worksheet

End If


Next Mysheet

End Sub