Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Easier way of adding new sheets to workbook
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Easier way of adding new sheets to workbook
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I protect Excel Workbook Structure except adding new sheets | Excel Worksheet Functions | |||
Easier way of adding new sheets to workbook | Excel Programming | |||
Easier way of adding new sheets to workbook | Excel Programming | |||
Adding sheets to workbook | Excel Worksheet Functions | |||
adding accross sheets in a workbook | Excel Worksheet Functions |