Need code help -- paste from multiple sheets to summary

[AngelK]

VB novice here. I am working on a code to pull one column of multiple sheets into a "summary" sheet. My workbook has sheets: summary, teacher, template, and 20-30 student sheets (varies). I need a code to copy each result (not blank cells) in column Q of each student sheet, and paste it under that student's column in the appropriate row on the Summary sheet.

My current code is able to pull the column Q from each student sheet, but it doesn't place it in the correct column in my summary (O7), and I it pulls in the entire column Q, even the blanks. The goal is to have the "grade" pulled into the matching question for each student.

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

here is a dropbox link for my file: https://www.dropbox.com/s/jin9dkuxu9...2).xlsm?dl=0

Thank you!

 

[AngelK]

Thank you so much! And thank you for making the instructions so clear for me. I cannot tell you how much I appreciate your help.