Identifying and appending a date to data

Hello,

I am dealing with data that is provided to me daily. It is organized into tables, with a merged header row (spanning 17 columns). The heading has the date for whatever day the data was provided. The format of the heading is always as follows: "Claims Summary (BW) for DD-Month-YYYY". For example - data provided on January 6th will read as "Claims Summary (BW) for 06-Jan-2015" (of course, no quotations. I provided them).

The tables contain the data for that day (99% of the time the table is 17 rows of data). I have created Macros that takes all the daily data tables by month, and copy/pastes the tables on top of one another. I then have a macros that identifies the rows I need and copies/pastes them to an aggregated data sheet, from which I have pivots pulling from.

I would like to automate this process completely. Basically, when the data is identified, copied, and pasted from the "stacked" tables, the dates for the data need to be provided as well. For example: If the data was from January 6th 2015. The header for that daily data table would read as "Claims Summary (BW) for 06-Jan-2015". So for all data rows copied from that table, each of those rows would need to be marked with that date (1/6, or January 6th, etc.). For the next table for January 7th, the data for that table would need to be marked with that date. So on and so forth. All that is needed is the dates to be placed in the column next to the last column of the table (column 18).

This would need to be done in a loop so that it would go through all the stacked tables for whatever month i designated, and copy paste the data into the consolidated sheet with appended dates.

I have provided an example of each of the three stages I am talking about:

1. The daily data tables as I receive them
2. The stacked tables (done by a macro). Data is copied from this sheet.
3. The consolidated data sheet. The data is pasted into this sheet

I have everything from this process automated except for this function! Any help would be appreciated!

Thank you,

- J