Hey SamT! Thank you so so so much for reaching out with a solution here. I am very sorry for not providing any example data.
Here is a workbook full of example data -> It is 5.0mb due to pictures for the metadata so here is a link to the file in my AWS S3 cloud -> https://cloud.jettlifetech.com/excel...+workbook.xlsm
Here is a highly compressed one with all other functions removed and stripped bare to send through this forums attachment manager. --> metacheck-vbapowered-compressed.xlsm

I tried setting up the code you provided, but I keep getting an error on "FinalURLtable(0, 1) = Split(SiteNameCell, ":")(1)" and your code is very clean but complex for me to understand or debug. Hopefully you can help explain? Again, i really appreciate the help here!