How can I to split data in WS Sheet Column 'D' into Columns 'C' thru last column
and place it under the right date. For example the Template sheet would have all the
team schedules under the date they play, the court they play on and the time.
A snippet of my macro thus far is shown at the end. It creates the data in the template sheet as shown in the example. But, I am lost on an approach to move the team data from the WS sheet under the matching date column along with getting the team data in the correct time and court place.
WS Sheet example looks like this
Col A Col B Col C Col D
[TABLE="width: 295"]
<colgroup><col><col><col span="2"></colgroup><tbody>[TR]
[TD]Date[/TD]
[TD]Time[/TD]
[TD]Courts[/TD]
[TD]Teams[/TD]
[/TR]
[TR]
[TD]05/07/2019[/TD]
[TD]10:00 AM[/TD]
[TD]1[/TD]
[TD]1 v 9[/TD]
[/TR]
[TR]
[TD]05/07/2019[/TD]
[TD]10:00 AM[/TD]
[TD]2[/TD]
[TD]3 v 5[/TD]
[/TR]
[TR]
[TD]05/07/2019[/TD]
[TD]10:00 AM[/TD]
[TD]4[/TD]
[TD]8 v 10[/TD]
[/TR]
[TR]
[TD]05/07/2019[/TD]
[TD]10:00 AM[/TD]
[TD]5[/TD]
[TD]7 v 6[/TD]
[/TR]
[TR]
[TD]05/07/2019[/TD]
[TD]10:00 AM[/TD]
[TD]6[/TD]
[TD]11 v 4[/TD]
[/TR]
[TR]
[TD]05/14/2019[/TD]
[TD]10:00 AM[/TD]
[TD]1[/TD]
[TD]7 v 5[/TD]
[/TR]
[TR]
[TD]05/14/2019[/TD]
[TD]10:00 AM[/TD]
[TD]2[/TD]
[TD]6 v 10[/TD]
[/TR]
[TR]
[TD]05/14/2019[/TD]
[TD]10:00 AM[/TD]
[TD]3[/TD]
[TD]2 v 9[/TD]
[/TR]
[TR]
[TD]05/14/2019[/TD]
[TD]10:00 AM[/TD]
[TD]4[/TD]
[TD]8 v 4[/TD]
[/TR]
[TR]
[TD]05/14/2019[/TD]
[TD]10:00 AM[/TD]
[TD]5[/TD]
[TD]1 v 11[/TD]
[/TR]
</tbody>[/TABLE]
Template sheet example looks like this
Col A Col B Col C Col D Col E
[TABLE="width: 347"]
<colgroup><col><col><col span="3"></colgroup><tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD="align: right"] 05/07/2019 [/TD]
[TD="align: right"]05/14/2019 [/TD]
[TD="align: right"]05/21/2019[/TD]
[/TR]
[TR]
[TD]Time [/TD]
[TD]Court[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10:00 [/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]6[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7:00 [/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]6[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
Sub Mrexcel()
Dim cl As Range
Dim s1 As Integer
Dim s2 As Integer
Dim s3 As Integer
Dim s4 As Integer
'Copy unique dates from WS Sheet to Template sheet & convert them to rows
Sheets("WS").Activate
With CreateObject("scripting.dictionary")
For Each cl In Range("A2", Range("A" & Rows.Count).End(xlUp))
.Item(cl.Value) = Empty
Next cl
Sheets("Template").Range("c3").Resize(, .Count).Value2 = .keys
End With
'Move Date & Time From WS Sheet to Template Sheet
s2 = 5
s4 = 5
For s1 = 1 To NumofTimes
Sheets("template").Range("A" & s2) = Sheets("WS").Range("F" & s1 + 1)
For s3 = 1 To NumofCourts
Sheets("template").Range("B" & s4) = Sheets("WS").Range("G" & s3 + 1)
s4 = s4 + 1
Next s3
s2 = 5 + NumofCourts
Next s1
End Sub
and place it under the right date. For example the Template sheet would have all the
team schedules under the date they play, the court they play on and the time.
A snippet of my macro thus far is shown at the end. It creates the data in the template sheet as shown in the example. But, I am lost on an approach to move the team data from the WS sheet under the matching date column along with getting the team data in the correct time and court place.
WS Sheet example looks like this
Col A Col B Col C Col D
[TABLE="width: 295"]
<colgroup><col><col><col span="2"></colgroup><tbody>[TR]
[TD]Date[/TD]
[TD]Time[/TD]
[TD]Courts[/TD]
[TD]Teams[/TD]
[/TR]
[TR]
[TD]05/07/2019[/TD]
[TD]10:00 AM[/TD]
[TD]1[/TD]
[TD]1 v 9[/TD]
[/TR]
[TR]
[TD]05/07/2019[/TD]
[TD]10:00 AM[/TD]
[TD]2[/TD]
[TD]3 v 5[/TD]
[/TR]
[TR]
[TD]05/07/2019[/TD]
[TD]10:00 AM[/TD]
[TD]4[/TD]
[TD]8 v 10[/TD]
[/TR]
[TR]
[TD]05/07/2019[/TD]
[TD]10:00 AM[/TD]
[TD]5[/TD]
[TD]7 v 6[/TD]
[/TR]
[TR]
[TD]05/07/2019[/TD]
[TD]10:00 AM[/TD]
[TD]6[/TD]
[TD]11 v 4[/TD]
[/TR]
[TR]
[TD]05/14/2019[/TD]
[TD]10:00 AM[/TD]
[TD]1[/TD]
[TD]7 v 5[/TD]
[/TR]
[TR]
[TD]05/14/2019[/TD]
[TD]10:00 AM[/TD]
[TD]2[/TD]
[TD]6 v 10[/TD]
[/TR]
[TR]
[TD]05/14/2019[/TD]
[TD]10:00 AM[/TD]
[TD]3[/TD]
[TD]2 v 9[/TD]
[/TR]
[TR]
[TD]05/14/2019[/TD]
[TD]10:00 AM[/TD]
[TD]4[/TD]
[TD]8 v 4[/TD]
[/TR]
[TR]
[TD]05/14/2019[/TD]
[TD]10:00 AM[/TD]
[TD]5[/TD]
[TD]1 v 11[/TD]
[/TR]
</tbody>[/TABLE]
Template sheet example looks like this
Col A Col B Col C Col D Col E
[TABLE="width: 347"]
<colgroup><col><col><col span="3"></colgroup><tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD="align: right"] 05/07/2019 [/TD]
[TD="align: right"]05/14/2019 [/TD]
[TD="align: right"]05/21/2019[/TD]
[/TR]
[TR]
[TD]Time [/TD]
[TD]Court[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10:00 [/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]6[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7:00 [/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]6[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
Sub Mrexcel()
Dim cl As Range
Dim s1 As Integer
Dim s2 As Integer
Dim s3 As Integer
Dim s4 As Integer
'Copy unique dates from WS Sheet to Template sheet & convert them to rows
Sheets("WS").Activate
With CreateObject("scripting.dictionary")
For Each cl In Range("A2", Range("A" & Rows.Count).End(xlUp))
.Item(cl.Value) = Empty
Next cl
Sheets("Template").Range("c3").Resize(, .Count).Value2 = .keys
End With
'Move Date & Time From WS Sheet to Template Sheet
s2 = 5
s4 = 5
For s1 = 1 To NumofTimes
Sheets("template").Range("A" & s2) = Sheets("WS").Range("F" & s1 + 1)
For s3 = 1 To NumofCourts
Sheets("template").Range("B" & s4) = Sheets("WS").Range("G" & s3 + 1)
s4 = s4 + 1
Next s3
s2 = 5 + NumofCourts
Next s1
End Sub