Hi There,

Thanks so much for that - typically, I have not explained part of the
problem very well. The Competitor issue I can see will work brilliantly.

The Judge thing. Lets call the original score sheet Template. This
contains the basic score sheet for one judge only. What I hope to do is to
repeat that at the bottom of the current 34 lines so if there are 3 judges
the sheet would show basic score sheet x 3 vertically. with a different
judges name in A2,A36 and A71. And then so on if there were 4 or 5 judges.
That sheet then becomes the template for all the competitors sheets, (who are
judged by these 3/4/ or 5 judges) when they are created. Is that possible do
you think ?

Thanks

Sybs


"Joel" wrote:

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


"SYBS" wrote:

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

Thanks very much Joel, will have a go at that and see how it comes out.
Thanks again

Sybs

"Joel" wrote:

Everything is possible. I think you need a matrix of competitor and judges
on the Main page.

Judge 1 Judge 2 Judge 3 Judge 4 Judge 5

Comp 1 x x x

Comp 2 x x
x

Comp 3 x x x

Comp 4 x x
x

Comp 5 x x
x

The judges templet would have all judges. Then using the above matrix make
a worksheet for each of the competitors that have the three judges that
scored the competior.

Copying ranges from 1 worksheet to another is pretty simple.

"SYBS" wrote:

Hi There,

Thanks so much for that - typically, I have not explained part of the
problem very well. The Competitor issue I can see will work brilliantly.

The Judge thing. Lets call the original score sheet Template. This
contains the basic score sheet for one judge only. What I hope to do is to
repeat that at the bottom of the current 34 lines so if there are 3 judges
the sheet would show basic score sheet x 3 vertically. with a different
judges name in A2,A36 and A71. And then so on if there were 4 or 5 judges.
That sheet then becomes the template for all the competitors sheets, (who are
judged by these 3/4/ or 5 judges) when they are created. Is that possible do
you think ?

Thanks

Sybs


"Joel" wrote:

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


"SYBS" wrote:

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