Help a Swim Team, worksheet function issue
 

I am creating a workbook that records a swimmer's times, determines
the fastest times for that swimmer, sorts the swimmers into age groups
and then determines the best combination of swimmers for a relay.

I am stuct on the ability to use a worksheet function combined with a
Select Case structure and the copy function.

The cells seem to be selected, but the actual paste fails to happen.

Sub AgeGroup()
'


Dim WkSht As Worksheet

For Each WkSht In ActiveWorkbook.Worksheets

Age = WkSht.Cells(2, 2).Value 'This cell contains the age of
the swimmer
Name = WkSht.Cells(1, 1).Value 'This cell contains the name of
the swimmer

If Age 100 Then GoTo Continue


'Controls the row fastest times are pasted in on Age Group
Wkshts (Not the Name)
T = 5 ' 8 and Under
U = 5 ' 9-10 swimmer count
V = 5 ' 11-12
W = 5 ' 13-14
X = 5 ' 15-18 count


Select Case Age

Case 0 To 8.99
GoTo Continue

Case 9 To 10.99
Range("C5:Y5").Select 'Should select the row of thefastest
times on this worksheet
Range("Y5").Activate
Selection.Copy
Sheets("Age Group 9-10").Select
Range(Cells(U, 3)).Select ' U is the row the swimmers
times are pasted
ActiveSheet.Paste
U = U + 1



Case 11 To 12.99 'These will be completed once this %$#@@3
thing works!
GoTo Continue

Case 13 To 14.99 'These will be completed once this %$#@@3
thing works!
GoTo Continue

Case 15 To 18.99 'These will be completed once this %$#@@3
thing works!

GoTo Continue

End Select



Continue:
Next WkSht


End Sub

Help a Swim Team, worksheet function issue
 

Sub AgeGroup()
'


T = 5 ' 8 and Under
U = 5 ' 9-10 swimmer count
V = 5 ' 11-12
W = 5 ' 13-14
X = 5 ' 15-18 count

Dim WkSht As Worksheet

For Each WkSht In ActiveWorkbook.Worksheets
' don't process sheets with Age in the name
if instr(1,wksht.name,"age",vbTextCompare) = 0 then
Age = WkSht.Cells(2, 2).Value
Name = WkSht.Cells(1, 1).Value
If Age 100 Then GoTo Continue


Select Case Age

Case 0 To 8.99
WkSht.Range("C5:Y5").Copy _
Sheets("Age Group 8 and Under").Cells(T, 3)
T = T + 1
Case 9 To 10.99
WkSht.Range("C5:Y5").Copy _
Sheets("Age Group 9-10").Cells(U, 3)
U = U + 1
Case 11 To 12.99
WkSht.Range("C5:Y5").Copy _
Sheets("Age Group 11-12").Cells(V, 3)
V = V + 1
Case 13 To 14.99 'These
GoTo Continue

Case 15 To 18.99
GoTo Continue

End Select
End if


Continue:
Next WkSht


End Sub

--
Regards,
Tom Ogilvy

"Todd" wrote:

I am creating a workbook that records a swimmer's times, determines
the fastest times for that swimmer, sorts the swimmers into age groups
and then determines the best combination of swimmers for a relay.

I am stuct on the ability to use a worksheet function combined with a
Select Case structure and the copy function.

The cells seem to be selected, but the actual paste fails to happen.

Sub AgeGroup()
'


Dim WkSht As Worksheet

For Each WkSht In ActiveWorkbook.Worksheets

Age = WkSht.Cells(2, 2).Value 'This cell contains the age of
the swimmer
Name = WkSht.Cells(1, 1).Value 'This cell contains the name of
the swimmer

If Age 100 Then GoTo Continue


'Controls the row fastest times are pasted in on Age Group
Wkshts (Not the Name)
T = 5 ' 8 and Under
U = 5 ' 9-10 swimmer count
V = 5 ' 11-12
W = 5 ' 13-14
X = 5 ' 15-18 count


Select Case Age

Case 0 To 8.99
GoTo Continue

Case 9 To 10.99
Range("C5:Y5").Select 'Should select the row of thefastest
times on this worksheet
Range("Y5").Activate
Selection.Copy
Sheets("Age Group 9-10").Select
Range(Cells(U, 3)).Select ' U is the row the swimmers
times are pasted
ActiveSheet.Paste
U = U + 1



Case 11 To 12.99 'These will be completed once this %$#@@3
thing works!
GoTo Continue

Case 13 To 14.99 'These will be completed once this %$#@@3
thing works!
GoTo Continue

Case 15 To 18.99 'These will be completed once this %$#@@3
thing works!

GoTo Continue

End Select



Continue:
Next WkSht


End Sub