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 -
Check the final non-empty cells for ranges $C$15:$FF$15 and $C$22:$FF$22 for all worksheets, except "DIRECTORY" and "TEMPLATE."
If the final cell in $C$15:$FF$15 is above 34, add the worksheet name to the list in range H:H on the "DIRECTORY" page.
Create hyperlinks for each sheet name.
If the final cell in $C$15:$FF$15 is below 26, add the worksheet name to the list in range I:I on the "DIRECTORY" page.
Create hyperlinks for each sheet name.
If the final cell in $C$22:$FF$22 is above 36, add the worksheet name to the list in range J:J on the "DIRECTORY" page.
Create hyperlinks for each sheet name.
If the final cell in $C$22:$FF$22 is below 36m add the worksheet name to the list in range K:K on the "DIRECTORY" page.
Create hyperlinks for each sheet name.
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.
Bookmarks