I have a sheet that represents a score card for a sporting event. Row 3 contains the dates of the game, row 4 contains the day of the week and row 5 contains a ‘points’ heading. The remaining rows contain the scores of the games and the team name
For example.
For Each mycell In Sheets("Scores").Range("B3", _
Sheets("Scores").Cells(3, LastCol))
If mycell.Value > LDate Then
Sheets("Scores").Range(mycell, mycell.Offset(0, -2)).Copy _
Destination:=Sheets("Standings").Range("H3"
Etc. etc
But, I am at a loss on how to move from the active cell in the date row down to row 6 and select the previous 3 columns and the rows (6-23) that contain the scores. The closest that I can is coping 1 column of scores to the different sheet using the following.
mycell.EntireColumn.Resize(Rows.Count - 5).Offset(5).Copy _
' Destination:=Sheets("standings").Range("H4")
A sample of my sheet is below. How would I search for a date, once found, copy the values and date to a new sheet for a total of 3 dates?
<tbody>
</tbody>
For example.
- Row 3 contains 01/01/19, 01/08,19, 01/15/19 etc. for 19 weeks
- Row 4 contains Wed, Wed, Wed, etc.
- Row 5 contains pts, pts, pts, pts, etc.
- Row 6 thru 23 contains Team name (col1), Scores(col2 to end)
For Each mycell In Sheets("Scores").Range("B3", _
Sheets("Scores").Cells(3, LastCol))
If mycell.Value > LDate Then
Sheets("Scores").Range(mycell, mycell.Offset(0, -2)).Copy _
Destination:=Sheets("Standings").Range("H3"
Etc. etc
But, I am at a loss on how to move from the active cell in the date row down to row 6 and select the previous 3 columns and the rows (6-23) that contain the scores. The closest that I can is coping 1 column of scores to the different sheet using the following.
mycell.EntireColumn.Resize(Rows.Count - 5).Offset(5).Copy _
' Destination:=Sheets("standings").Range("H4")
A sample of my sheet is below. How would I search for a date, once found, copy the values and date to a new sheet for a total of 3 dates?
17-Sep | 24-Sep | 1-Oct | 8-Oct | 15-Oct | 17-Oct | 22-Oct | |
Tuesday | Tuesday | Tuesday | Tuesday | Tuesday | Thursday | Tuesday | |
Pts | Pts | Pts | Pts | Pts | Pts | Pts | |
1-Team Name | 1 | 1 | 1 | 1 | 1 | 1 | 1 |
2-Team Name | 1 | 1 | 1 | 1 | 1 | 1 | 1 |
3-Team Name | 1 | 1 | 2 | | | 1 | 2 |
4-Team Name | 2 | x | 1 | | | 2 | 2 |
5-Team Name | 2 | 2 | 2 | | | | 2 |
<tbody>
</tbody>