How to Scan All Worksheets in a Certain Range, Compile Lists of WS names

I would like to set up a macro that scans certain ranges in all worksheets in a workbook. Specifically, it should look at the LAST non-blank cell in the ranges. The names of the tabs of that data should then go into a hyperlinked list on the "DIRECTORY" worksheet, 4 separate lists total.

My VBA proficiency is nowhere near ready to compile this macro, but I will search for pieces of it, maybe string something together. Any help is greatly appreciated.

These are the ranges that should be scanned - "CLIENT_ORS_TOTAL" (C15:FF15) and "CLIENT_SRS_TOTAL" (C22:FF22). If the 'CLIENT_ORS_TOTAL" is above 34, it should go into the hyperlinked list (H:H), "Client ORS Above Clinical Significance." If it is under 26, it should go into the hyperlinked list (I:I), "Client ORS Under Clinical Significance." If the range, "CLIENT_SRS_TOTAL" is above or equal to 36 (J:J), it should go under the hyperlinked list, "Client SRS Above Clinical Significance." If it is under 36, it would go under "Client SRS Under Clinical Significance" (K:K). All of these are on the 'DIRECTORY" worksheet.

I've been abusing this form looking for answers and have been very satisfied with the responses. I would love to upload my workbook once its done for those that are curious. I'm pretty proud of it.

K, I attached the file. It's from my work and I could not upload it from there. I emailed it to my Mac at home, uploaded it from home. The macros were missing while trying to use the workbook at home, so I don't know if the attached file is the full MS Excel version, or the Mac one I just viewed.

I added some comments on the "directory" page which should hopefully clear up what I am looking for.

I need a macro that will check the last non-empty cell in the 2 ranges that I have specified for each worksheet, except for "directory" and "template." If those non-empty cells meets certain criteria, it should collect those values and import that spreadsheet's name into one of the four lists on the "directory" page in hyperlinked form.

In plain english, the VBA may look like this -

Please Login or Register  to view this content.

The final non-empty cell for these ranges will be constantly updated as well, so it's not a fixed cell.

I hope this helps! Anything would be appreciated, I am close to being done with this workbook and have reached a point that is well beyond my proficiency.