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!

 

[Michael M]

The dropbox link doesn't work I'm afraid !

 

[jolivanes]

Assumes that the Column Headers in your Summary sheet are the same as your sheet names.
No Error checks in code
Change all your references as required
Try it on a copy of your workbook as this does only a part of your requirement as I understand it.

Sub Try_This()
    Dim i As Long
    Application.ScreenUpdating = False
    For i = 2 To ActiveWorkbook.Worksheets.Count    '<---- Change as required
        With Sheets(i)
            .Range("Q1:Q" & .Cells(.Rows.Count, "Q").End(xlUp).Row).SpecialCells(2).Copy
            Sheets("Summary").Rows(1).Find(Sheets(i).Name, LookIn:=xlValues).Offset(1).PasteSpecial xlPasteValues
        End With
    Next i
    Application.ScreenUpdating = True
End Sub

 

[AngelK]

I've recreated the link - https://www.dropbox.com/s/th6ll48o1bxnnc9/Report Cards - draft 82316.xlsm?dl=0

Thank you for your time!

 

[AngelK]

Thank you for helping -- when I use the above code, I'm getting a "this command cannot be used on multiple selections" on this row:
.Range("Q1:Q" & .Cells(.Rows.Count, "Q").End(xlUp).Row).SpecialCells(2).Copy

 

[Michael M]

I'm guessing that's because cells M to Q in rows 95,96 & 97 are merged ??
Unmerge them and see what happens
One thing you must try and avoid at all costs is using merged cells !!!
In the case of the above highlight M95 to Q95....unmerge them, then while they are still highlighted try
Rightclick>>format>>Allignment tab>>Horizontal dropdwon>>"center across selection"

Repeat the process for rows 96 & 97
Then retry the code provided by jolivanes

 

[jolivanes]

Thank you Michael M.
I had not seen the attachment but only reacted on what I thought the OP meant.
Thanks again

 

[Michael M]

@jolivanes
Glad to help....but I haven't really looked at the whole issue.
I had assumed you had seen the link and fufilled the OP's request.....I guess we'll find out soon enough...

 

[AngelK]

I'm guessing that's because cells M to Q in rows 95,96 & 97 are merged ??
Unmerge them and see what happens
One thing you must try and avoid at all costs is using merged cells !!!
In the case of the above highlight M95 to Q95....unmerge them, then while they are still highlighted try
Rightclick>>format>>Allignment tab>>Horizontal dropdwon>>"center across selection"

Repeat the process for rows 96 & 97
Then retry the code provided by jolivanes

Thank you jolivanes and Michael M. Sorry for the delay, mrexcel has been down for me all day.
I apologize for the merged cells. I have removed them, and tried the code again. I'm getting a "Run-time error '91': Object variable or With block variable not set". I've tried a view changes, but each attempt results in a different error. I'm learning VB by trial and error, and I really appreciate your help.

 

[jolivanes]

I had a look at your file but for some reason I am not able to safe it to the HD so I can try the code on it.
Did you check to make sure that the names in the Column Headers are the same as the Sheet names?
Did you check, and change where required, all the references to Cell addresses, Column Numbers/Names, Row numbers etc etc?