VB help - copy specific cells from one column of multiple sheets into one summary

[AngelK]

I'm new to VB, and need your help if you have time. I have reviewed all posts that relate to my inquiry, and cannot make a code do what I need it to. My workbook has sheets: summary, teacher, template, and 30-40 other sheets (varies). I need a code to copy each result in column Q of each student sheet, and paste it under that student's column in the appropriate row on the Summary sheet.

Example: go to first student sheet smith1, joe1 (name will vary), copy cell Q14 (will always be Q14), paste to summary sheet at O7 (matching question#). Repeat this process for all rows for that student, putting the appropriate test mark on the matching question on summary sheet. Then, repeat this for the rest of the sheets. All the student sheets have the same format.

Here is my file: https://www.dropbox.com/s/6p9j7orer7swkc6/Report Cards - draft.xlsm?dl=0

I would post my current code, but it makes more of a mess to the sheet than it helps! I need to start over.

Thank you for your help!

 

[AngelK]

Still stumped on this one and looking for help. Thank you.

 

[AngelK]

I'm getting closer on my project, but am still needing some help if anyone is available. I am able to copy/paste values for each of my students, but I need help with: 1) getting it to paste into the correct column (should paste first result in O7, but it's pasting in C7), and 2) only want to copy/paste cells that contain "T1", "T2", "T3", or "NOT MET". This eliminates all the subquestions and gives only the final result for each section to match the summary.

Here is my link to dropbox: https://www.dropbox.com/s/jin9dkuxu9osqal/Report Cards - draft (2).xlsm?dl=0

Here is my current code:

Sub Create_Summary3() 
Dim sh As Worksheet, sumSht As Worksheet
Dim i As Long
Dim emptyColumn As Long

Set sumSht = Sheets("Summary")
sumSht.Move after:=Worksheets(Worksheets.Count)

For i = 1 To Worksheets.Count - 3 'skips the non-student sheets
    
    Worksheets(i).Range("Q14:Q79").Copy
   sumSht.Cells(7, sumSht.Columns.Count).End(xlToLeft).Offset(, 1).PasteSpecial xlPasteValues
 
Next i

End Sub

Thank you in advance