Consolidating data from multiple tabs and spreadsheets from multiple users

Hello mruss_14 and Welcome to Excel Forum.
As this hasn't drawn a response I'll at least say something.
I am guessing that you want to do this using VBA because you want to erase the values in the daily sheets so that you can start fresh each week.
I would suggest that isn't an easy way to go about this.
Excel is set up for row over row data where each record might have a date, an attribute (such as Benefit questions), a value (such as 0.5) and a comment (such as Medical)
From that type set up the data could be analyzed using a pivot table.
If you expect a very large number of records each week, you might also be able to put each attribute on its own sheet and append them using Get & Transform, and possibly, since you are using the 365 version of Excel, load it to a data model (I believe a data model will hold about 99M records).
I'll wait to see if you have any interest before making further comments.
Let us know if you have any questions.

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

Also, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.