Hi, so every day I have to run a task where I open a helper spreadsheet, drop a load of data exports in from emails (reports linked in the email, not attached so I'm assuming there's no way to automate this element of it) and then go into a tracker spreadsheet (there is one per week, with tabs per day - I need the data for the previous day, whatever file that may be) and then copy the data from this spreadsheet into the helper to consolidate and give a report. I'm wondering if there is a way to run some VBA to pull this data in without having to open the specific spreadsheet? I initially tried this using Indirect( ) and a concatenate to build up dynamic cell references, but they wouldn't work on closed workbooks.
Ultimately it needs to use a date field to generate a file name in the format "Staffing wc dd-mm-yyyy" and copy data from the day of the week given from the date, and copy data from that tab using range DO8:DW157. EG if the date in A1 = 11/08/2021 it should copy data from the spreadsheet "Staffing wc 09-08-2021" and copy in range DOO8:DW157 from the tab "Wed" without opening it, pasting as values into A2. From my googling I believe it's possible but I'm struggling to do it.
I hope this makes sense, any suggestions gratefully received Thanks!
Ultimately it needs to use a date field to generate a file name in the format "Staffing wc dd-mm-yyyy" and copy data from the day of the week given from the date, and copy data from that tab using range DO8:DW157. EG if the date in A1 = 11/08/2021 it should copy data from the spreadsheet "Staffing wc 09-08-2021" and copy in range DOO8:DW157 from the tab "Wed" without opening it, pasting as values into A2. From my googling I believe it's possible but I'm struggling to do it.
I hope this makes sense, any suggestions gratefully received Thanks!