This should be a pretty straigh-forward Excel macro that we’re looking for; anybody have such a beast?
Two spreadsheet (workbooks)
Workbook A has one spreadsheet with a separate row for each match/team being scouted. Each row has multiple fields (columns).
Workbook B contains multiple tabs for each team
namely, we want to copy each row from workbook A into the respective “tab” in workbook B into a new row on that spreadsheet tab. If the row in workbook A doesn’t correspond to a current tab in workbook B, then create a new tabbed sheet in the master workbook.
I’ve googled-around for Excel Visual basic macros but can’t find this simple cut & paste routine.
Yes, i think the other way to do this is to simply dump all the scouting records into one huge flat spreadsheet and then use the “sort” and “subtotal” functions to group all the records by team number.
Since you know the teams ahead of time, you can create tabs for each team.
Or, you can avoid the tabs all together and create an interface page similar to what Karthik did here using MATCH and OFFSET.: http://www.chiefdelphi.com/forums/showthread.php?threadid=37101. This works well as long as all of the team’s data is next to each other. We created a simple macro that sorted the data by team number whenever we saved.
Here is that macro. You’ll of course need to change the ranges and column for your data.
' SortMD_Robot Macro
' Macro recorded 3/22/2006 by Zach Steele
Selection.Sort Key1:=Range("B4"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Cancel = False