VBA Copy and Paste Data from a daily file into another sheet

[mik1996]

Hi,

I have an important process in work which i am trying to simplify, I basically need to copy the data (highlighted yellow) in this sheet below and paste it into a new sheet which gets updated every day (it would be great if it could look at the date in a cell on the new sheet and get the information from the file on that particular date). The problem i've found is that the source data will change each day as its a daily workbook so the file name will change each day and folder location will change each month etc. ,
K:\Finance\Protected Funding Sheets\Barclays cash funding\Daily Funding Calculation\2023\04. April\24 April 2023 BCA CSH Daily.xlsx

I would ideally like it for the data to be pasted into a sheet like this below (or something similar) if that is possible at all

Any help on this at all would be really appreciated as it causes me a lot of hassle each day
Thanking you in advance,

 

[Fluff]

Duplicate to: Loop through folders and subfolders & copy and paste data from specific sheet and cells to another sheet

In future, please do not post the same question multiple times. Per Forum Rules (#12), posts of a duplicate nature will be locked or deleted.

In relation to your question here, I have closed this thread so please continue in the linked thread.