Save data from a live sheet that changes daily

[AndyGav86]

I have a worksheet that has two sheets that are used for a daily report and the rest are for the month, The daily reports are saved as a pdf and sent via email every night so the data will be wiped daily, but I need three cells of data (day totals) from the report to be saved somewhere so that I can collect the total for the month and then feed the Average of the month into another cell.

Hope that makes sense
Many Thanks in Advance

 

[jdellasala]

Have you tried importing the PDF through Power Query?

Once it's imported, you can transform it so that it contains only the data you need and load the resulting table. From there you can copy the sheet the table is on to another file (without the same sheet name!). The other file will pull in the Query, but the Query can be deleted and the Table will become static. If the name of the PDF doesn't change, you can just Refresh All and then run a VBA script (yes, I do use VBA on occasion!) to copy the sheet to the other file and delete the query in that file.

It may take a bit of time to get set up, but once it is it will save you a LOT of time!

(Please use XL2BB)