Simple spreadsheets in a work book from several locations to keep track of the number of Covid tests we have completed.

All worksheets in the workbooks are exactly the same.

Column A is blank at present
Column B is the date: so the whole column is filled with the dates from the 1st of October to the 31st of December.
Column C is the number of tests completed.

What I would like column A to show is the number of tests completed in the previous 7 days from now()
I'd then like to export the contents of Column A on the date of the report to a separate spreadsheet in the same workbook as a report.

I hope you can understand this

Cheers
Steve