I have a worksheet, "CMJ", that extracts data from "sheet 3" based on a name from a data validation range, on the "CMJ" Sheet. The extracted data is then run through additional equations, on "CMJ" Sheet and outputs new data that I want to copy and paste on another sheet, "DataSheet". The data validation, "Athlete_List", is on CMJ, cell N7. The values I want to copy are on "CMJ" cells T3 to AH3, and I want to copy them to "DataSheet", starting in cell A2, and pasting in the row on the bottom.
I had started with the idea that the code would compare the data validation list, "Athlete_List", with a specific list (This list is in column 7 on the "CMJ" Sheet) of names that should be selected from data validation list and data copied, to the "Data_Sheet" Sheet.
I hope that is enough detail. The code I currently have is below, but I cannot figure out the issue.
Sub Macro15()
Dim CMJ As Worksheet
Dim DataSheet As Worksheet
Dim Athlete_List As String
Dim rngitems As Range
Set CMJ = Sheet1
Set DataSheet = Sheet2
Set rngitems = Sheets("CMJ").Range("Athlete_List")
CMJ.Range("T3:AH3").ClearContents
For Each rngitems In Range("Athlete_List")
If Cells(i, 7) = Athlete_List Then
Range(Cells(i, 7), Cells(i, 27)).Copy
DataSheet.Select
Range("A200").End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
CMJ.Select
Next rngitems
End If
Next i
CMJ.Select
Range("N7").Select
End Sub
I had started with the idea that the code would compare the data validation list, "Athlete_List", with a specific list (This list is in column 7 on the "CMJ" Sheet) of names that should be selected from data validation list and data copied, to the "Data_Sheet" Sheet.
I hope that is enough detail. The code I currently have is below, but I cannot figure out the issue.
Sub Macro15()
Dim CMJ As Worksheet
Dim DataSheet As Worksheet
Dim Athlete_List As String
Dim rngitems As Range
Set CMJ = Sheet1
Set DataSheet = Sheet2
Set rngitems = Sheets("CMJ").Range("Athlete_List")
CMJ.Range("T3:AH3").ClearContents
For Each rngitems In Range("Athlete_List")
If Cells(i, 7) = Athlete_List Then
Range(Cells(i, 7), Cells(i, 27)).Copy
DataSheet.Select
Range("A200").End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
CMJ.Select
Next rngitems
End If
Next i
CMJ.Select
Range("N7").Select
End Sub