Auto Create New Worksheets with names based on Cell text and, copy template format to newly created worksheet, populate from Master Worksheet.

[aic7577]

I have created a fairly sophisticated XL workbook for youth sports drafts for up to 400 kids.

The workbook currently can:

Determine the entire draft order through the number of kids enterred based on two draft systems; Snake (1,2,3,4,5,5,4,3,2,1,1,2...etc) or Conventional (1,2,3,4,5,1,2,3,4,5,1,2...etc)) Display who is currently drafting, who is next up to draft, and who is "in the hole" For the coaches' kids, who have predetermined draft spots, the draft order factors them out so the draft order only displays open draft spots and excludes the spots that are already determined.

I have a dynamic Validation pick down list of available players that are to be drafted that auto-updates so only available players can be chosen from the list; the players already drafted fall off the pickdown list.

What I am trying to accomplish now is as follows:

When the coaches names are input in a column (I12-I23) which is currently used to determine the full draft order up to the number of kids in the draft (could be as few as 4 coaches or as many as 12), I would like to autocreate new worksheets titled with the coaches names (preferably starting at the end of the worksheet list. Then, copy over a predetermined worksheet template (column titles), and ultimately pupulate each coach's worksheet with each player drafted and their pertinent data from the Master Template Worksheet (names, DOB's, contact info, etc....).

I am more than willing to email my current draft workbook out that may add to understanding where I am at.

I have limited experience with macros...I know how to enter, execute and modify, but the actual programming gets a bit intense for me.

Using Excel 2002

Thanks in advance for every / anyone's consideration.

Cheers

Greg

aic7577@comcast.net

 

[aic7577]

bump

 

[lenze]

Create a sheet template ( A one sheet file) Name the sheet and file myTemp
Save it as a template. Now you can record a macro adding that sheet template by rightclicking a sheet tab and choosing insert. This will give you the path. Your code would look something like

Sheets.Add Type:= _
        "C:\Documents and Settings\user3\Application Data\Microsoft\Templates\myTemp.xlt"
        ActiveSheet.Name = Sheet1.Range("$A$1")

HTH
lenze

 

[orderemail]

So were you ever able to complete your project? I am in the process of creating a similar workbook and would love to see yours. Please let me know if you are still open to sharing!

Thanks,
Frank

I have created a fairly sophisticated XL workbook for youth sports drafts for up to 400 kids.

The workbook currently can:

Determine the entire draft order through the number of kids enterred based on two draft systems; Snake (1,2,3,4,5,5,4,3,2,1,1,2...etc) or Conventional (1,2,3,4,5,1,2,3,4,5,1,2...etc)) Display who is currently drafting, who is next up to draft, and who is "in the hole" For the coaches' kids, who have predetermined draft spots, the draft order factors them out so the draft order only displays open draft spots and excludes the spots that are already determined.

I have a dynamic Validation pick down list of available players that are to be drafted that auto-updates so only available players can be chosen from the list; the players already drafted fall off the pickdown list.

What I am trying to accomplish now is as follows:

When the coaches names are input in a column (I12-I23) which is currently used to determine the full draft order up to the number of kids in the draft (could be as few as 4 coaches or as many as 12), I would like to autocreate new worksheets titled with the coaches names (preferably starting at the end of the worksheet list. Then, copy over a predetermined worksheet template (column titles), and ultimately pupulate each coach's worksheet with each player drafted and their pertinent data from the Master Template Worksheet (names, DOB's, contact info, etc....).

I am more than willing to email my current draft workbook out that may add to understanding where I am at.

I have limited experience with macros...I know how to enter, execute and modify, but the actual programming gets a bit intense for me.

Using Excel 2002

Thanks in advance for every / anyone's consideration.

Cheers

Greg

aic7577@comcast.net