Identifying and appending a date to data

[jcroque89]

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

[JOHN H. DAVIS]

This will put the date in a string variable. Then you could probably add it too your other code. Hope that helps.

Please Login or Register  to view this content.

[jcroque89]

Where would I add it in here?

For k = 2 To LastRow
Select Case Cells(k, 2).Value
Case Is = "Inst (5010)", "Prof (5010)"
Range(Cells(k, 1), Cells(k, 17)).Copy Worksheets("Hello").Range("A" & Rows.Count).End(3)(2)
End Select
Next k

Also, could you please explain .End(3)(2). Just so I understand how it works exactly.

Thanks!

- J

[JOHN H. DAVIS]

Please use code tags when posting. Maybe:

Please Login or Register  to view this content.

.End(3)(2) - (3) is a shorter version of (xlUp) (2) is another way to do a row offset (ie the last row with data would be (1), row after (2)

[jcroque89]

Ok. Thank you for the explanation (regarding the End). Makes sense.

As for the macros, after I added LastRow = Range("A" & Rows.Count).End(xlUp).Row, it is performing the copy/paste with the criteria, but no dates are appended.

Are you sure Right([A1], 11) will work in this situation?

Thank you.

- J

[JOHN H. DAVIS]

Can you attach a sample to test on. The example provided doesn't seem to reflect what you are after. Right([A1], 11) worked for me when I tried to draw the date from your example. Test on your Sheet "Daily Table".

Please Login or Register  to view this content.