Consolidating worksheets and then referencing the worksheets themselves in a new column

First time posting on the forums, hopefully you fine people can help me out. A little background for context: I'm a co-op student trying to help a company with their payroll. The company employes temporary personel on a short-term basis (3-30 days). Because the crews stay the same once they arrive on site, once the first day's data has been entered, the range can essentially be copied and pasted onto the next day's tab while adjusting for overtime and anything else relevant.

Initially, the problem was how do I summarize the data most efficiently. I found a macro on this site that copies the contents of each worksheet and dumps the data onto a new sheet it creates which serves as a summary sheet. This is perfect considering each pay period the company is employing different people. I can then sort the data alphabetically, giving me a full summary of all the days each individual worked in the period.

However, I'm unable to tell which day each line of data relates to after I sort alphabetically. Manually, I can work around this by entering in the date into a new column prior to the data being sorted, propogating down, and repeating for each day. Obviously this is time consuming. What I would like is to reference the tab name (the date) and add this information to a cell in a column on the right. That way, each row would display the date to which it applies at the same time it's being copied onto the new sheet.

The macro I copied that consolidates all the days into one sheet is below:

Please Login or Register  to view this content.

Hopefully, there is an excel macro genius out there who understands what I'm trying to do. Thanks in advance, and remember: YOLO!

Do you have a sample file that you can upload which you are using with this code?