I created an Excel database to automatically create sheets for a variable number of teams. It uses the built in VB Editor, and creates a new Workbook each time.
Tell me your thoughts, I’m going to experiment some more to make this transferrable over many years.
Read about/get it here.
When I try to open the workbook, I get an error that states there is unreadable content and If I want to, I can click “Yes” to recover the contents of the workbook. When I do, I see a list of team numbers in the leftmost column of the first sheet, and everything else is blank. In the VBA editor, there are no macros.
I’m using Excel 2007. Is it something to do with that, or is there another setting I need to activate?
I don’t know, I’m using excel 2003 so I don’t know if that could be it. The list of team numbers on the left is just the list of teams going to the Portland, OR regional and is there to show where to put the team numbers, or as an example. I’ll reupload the file and see if that will fix it. I’ll also attach a text document with the actual VBA code so you could copy-paste if all else fails (or just to look at).
There have been a few updates to the Workbook, they are all still on the same paper. Let me know if you want a copy of the new VBA code, or if there are any new problems. As far as I can tell, this should be the final version. Check the Readme for information on the updates.
Nice program. I’ve got a few recommendations.
Your readme doc could use a little more explanation on how to use the system. For example, what values do your scouters put in their sheets?
At the beginning you ask for the number of teams. You can get this automatically with code similar to:
With Worksheets("Team List")
Set r = .Range(.Cells(2, "A"), .Cells(2, "A").End(xlDown))
End With
For Each cell In r
TeamNumber = cell.Value
' do something like create a worksheet with it
Next
It look like you are programmatically creating the team sheets. While this is a cool excercise in programming, I prefer to create a template sheet, then copy it. This allows me to use the built in WYSIWYG editor provided by Excel. The complete logic I’m using to create sheets from a list of team #'s is:
Sub Create_Teams()
’ Deletes existing team sheets, then creates one sheet per team represented in a
’ list of teams.
’
’ Assumes teams are in worksheet “Team List”,
’ and a team template sheet “Team Template” exists
Dim r As Range, cell As Range
’ Confirm this is what they want to do, sheets will be blown away
If (MsgBox(“Executing this routine will destroy the contents of any existing team sheets. Continue?”, vbYesNo, “Warning”) = vbYes) Then
Application.DisplayAlerts = False ’ get rid of annoying confirmation of delete for each
For Each Sheet In ThisWorkbook.Sheets
If (Left(Sheet.Name, 2) = “T-”) Then ’ if sheet begin with “T-” delete it
Sheet.Delete
End If
Next
Application.DisplayAlerts = True ’ turn alerts back on
With Worksheets("Team List") ' get a collection of cells, starting a A2, going down
Set r = .Range(.Cells(2, "A"), .Cells(2, "A").End(xlDown))
End With
For Each cell In r ' for each team #
LastSheet = Sheets.Count ' get last sheet #
TeamNumber = cell.Value ' copy team #
Sheets("Team Template").Copy After:=Sheets(LastSheet) ' copy template sheet to new sheet at end
Sheets(LastSheet + 1).Name = "T-" & TeamNumber ' rename sheet to T-<team #> '
Sheets(LastSheet + 1).Range("B1").Value = TeamNumber ' set one of the cells in new sheet to team #
Next
End If
End Sub
I’m no expert on programming Excel. This is just a bunch of code I’ve cobbled together based on previous VB programming experience and lots of Google searches. There is probably plenty of room for improvement on this code.
Good luck
Thanks for your suggestions. I knew there was a way to get the number of cells in a column/row but I don’t know the exact code. That’s why I went with the inputbox. I did make a template sheet for each team sheet, but I then transcribed it to VBA (it took forever). Because I am creating an entirely new workbook, this was the “easiest” way without having to worry about cross-copying from one workbook to another. I know that that is relatively easy, but this way I can add (in the future) something to automate the different scoring types (last year we had herding, hurdling, racing; this year we have only moon rocks, empty cells). That’s something I plan do to over the summer. I’ll try to make the readme a little more user friendly and I’ll put that up sometime over this weekend.
That should cover everything.
Version 2.4 is out. I have rewritten the readme to make it a little easier to read (hopefully) and updated a couple things. Thanks for the suggestions.
Just as an update, I have been working on this, and it I will upload it as soon as I have it finished, for your review.
If you’re confused about the quoted statement, what I mean is that, every year we have a different challenge. So every year we have to remake the scouting system for that year, to incorporate the new scoring types. My scouting macro will auto-create the workbook based on user input. This way, you will drastically cut down on time and the amount of mistakes you might possibly make. This is similar to the team numbers sheet in my previous macro, where you can have as many, or as few, teams as you want, and the macro will make the workbook for that regional.
Currently, I am about 50% down with the new program. Please tell me if you think I should add/remove anything, and what you think of the project in general.
I should be done with the program by Christmas (about 90% done now), and then I’ll upload it here. Until then, you can download what I’m currently working on here. It will give you an idea of what to expect.
If you have any questions, or want me to add/remove anything, just email me/pm me/post here.
EDIT:
To view the readme, click here. Like with Scouting Template.xls, this is by no means the final version, and is constantly being updated. If you have any questions about wording, PLEASE voice them, I am trying to make this as user friendly as possible.