Hello there!
Your files are missing from submission, but you probably know that already :)
Anyhow, I would like to propose to you slightly different angle on the task. Why don't we bring your sheet to Google spreadsheets instead of trying to turn mountains with VBA. Google Sheets have built-in sraping tools, which can do the job whenever you open the file or you can even schedule the tasks to run any time you want.
Also, I don't know the details, but if you are using data for generating some graphs or perhaps customer reports, then that can be all automated on a whim!
E-mailing, scheduling, pdf generation, saving into folders, syncing to you PC/Mac - all possible.
All that besides the point that you can open it in any browser and have access to it on various devices.
I hope I triggered some interest. Once I see your files, I'm sure I can find ways to make your workflow better.
Let me know if you wish my help.
Cheers,
Denis