VBA help for autosorting rows, moving blank function rows to bottom (not delete), & consolidating 4 sheets to another sheet

[Jason Chan]

I run a tuition center and at the moment, I only have 10 students. The center offers three subjects only - ENGLISH, MATH and HISTORY. Scores are aggregated by test components, there are total of seven components in my center. Each subject utilizes a portion of the test components which accords scores for every test taken to each student (ENG – 4 comps, MATH – 4 comps, HIST – 2 comps). The three subjects share one to two test components so some components are retaken with altered questions. All students enroll into ENG, but not all students enroll into MATH and HIST.

SHEET1 is the manual main tests data entry worksheet and has about twenty columns covering all components scores for all ENG, MATH. HIST. SHEET2 is the voluntary work from students’ own practical work which do not add to their overall scores, but provide a reference for my end of semester award. I have to manually enter each student’s scores in all ENG, MATH and HIST components test scores into SHEET1 after every test I give them, as well as SHEET2 after some of them offer to get together to form smaller voluntary projects.

Then, I create SHEETS 3,4,5 to automatically populate with a simple =IF(…,ELSE,””) function pasting them into rows 1 to 1000 in each of these three sheets 3,4,5 (1000 is the temporary number of rows that I estimate my tuition may grow to need for the foreseeable years). SHEET3, SHEET4 and SHEET5 are for auto-populating from SHEET1, filling only rows who meet certain criteria. I do not plan to manually enter anything into SHEETs 3,4,5. After they self-populate every time, my 1st problem is, I need each sheets 3,4,5 to all be instantly auto-sorted ascendingly by the date column in column C, all rows consolidated (no blank rows in-between the 1st and last filled rows) and rows that although look blank but with function to automatically go beneath the last filled row, but not eliminating or deleting those blanks with functions.

Finally, comes SHEET6, which is my 2nd problem, I need to automatically populate continuously by consolidating and merging ALL rows from SHEETs 2,3,4,5 via VBA, then continuously auto-sorting all these rows on SHEET6 whenever a new entry is made in SHEET1 or SHEET2, sorted by the students’ ID in column B. So, the total no.of rows in SHEET6 would be the total combined of all rows in SHEETs 2,3,4,5.

Sadly, I have tried some depressing attempts on my own and a couple of assisted ones, but none have really helped me. For instance, I tried a code to sort the auto-populate sheets, but my function rows that are still blank, all get sorted to the top, while still showing blank. Some people offered me codes, but those codes delete my functions rows that return blanks. I don't want those blanks deleted. I need the filled function rows that are despite not populated, to just be sorted to the bottom, while the filled names and scores rows to the top. And, I have not gotten any help so far for sorting & consolidating the auto-populated sheets to SHEET6 yet. I’m not VBA fluent. I will appreciate if there are any kind expert out here who could help me with a VBA code to complete the above requests for my SHEETs 3,4,5,6?

I have created a cropped miniature sample workbook (of my actual workbook) but I can't find any location here to upload. Please let me know if needed and how to show you.


Waiting for the kind expert at my center,
Jason .

 

[Jason Chan]

Hi Koen,

I get you, I decided to settle on a button. I think I can click on it everytime I need to update the Result sheet.


Koen, a command line you wrote:
ShtRes.Cells.ClearContents

can you help me to tweak the above code, so that it will only clear everything in the Result sheet but not column "J" and the top Header row. I need col "J" and top header row that I designed and formated to be left un-touched by the code everytime the button is clicked. Right now, it is erasing these 2 places each time I click it.

I just need to maintain the new col "J" where i already wrote a simple IF...ELSE function for all the populated rows. Also, I had inserted manually a new row above the header row to write some title with format.


Jason.

Hi Jason,

normally code won't auto-run. Code is generally located in Modules and triggered by a button. What you could do is link the code to the activation of the worksheet with the totals. But you wouldn't want to let this macro run everytime you change the contents of a cell, that would make it near impossible to work with that workbook.
So how to link it to the activation of the sheet? -> if you go to VBA (ALT+F11), you'll notice that there are basically 3 different places for a macro:
-in a sheet-object
-in the workbook-object
-in a module
Best practice is to put most of your code in modules and as little as possible in the other 2. If you select your summary-sheet in VBA, there should be 2 dropdown boxes at the top, select "worksheet" in the left and "activate" at the right and Excel/VBA will create a block like so:

Private Sub Worksheet_Activate()

If MsgBox("Are you sure?", vbYesNo) <> vbYes Then
    Exit Sub
End If

Call YourMacroName

End Sub

I added a bit in the middle, which shows a popup-box, so you can choose whether you want to run the macro or not.

Hope that helps,

Koen