Macro/VBA Help with Attempting to Consolidate Multiple Worksheets into One.... with a Twist

[doublejtacoma]

Hello there,

I seek your help. I have scoured this forum, along with a few others, as well as some YouTube videos, and I can't seem to find out exactly what I am looking for.

I am looking to some data consolidation; please allow me to elaborate on what I am trying to achieve. What I have is a daily worksheet template that I use at work each day. So I end up with 365 worksheets per year, and that's fine for my record keeping, but what I am interested in doing is consolidating a month's worth of daily sheets into one Monthly worksheet. To simplify this consolidation, I am only interested in copying data from a few cells from each daily sheet. Here is a sample of the Daily Template:

Excel 2002

	A	B	C	D	E	F	G	H	I
DESCRIPTION
BANK DEPOSIT
POS CREDIT
POS DEBIT
LOTTERY PAID OUT
CONFECTIONARY PAID OUTS
MAINTENANCE PAID OUT
VEHICLE EXP PAID OUT
GST on Paid Outs
ESSO EXTRA REDEM
CHARGES
US$ DEPOSIT
US EXCHANGE
DRIVEAWAYS
CREDITS
LOTTERY REFUNDS
GIFT CARDS (prepaid card purchases)
GST COLLECTED
TOTALS
CASH OVER/SHORT

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]COMPANY XYZ[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]2[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]DAILY SALES REPORT DATE:[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="bgcolor: #CCFFCC, align: center"][/TD]
[TD="bgcolor: #CCFFCC, align: center"][/TD]
[TD="bgcolor: #CCFFCC, align: center"][/TD]

[TD="align: center"]3[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]ACCOUNT[/TD]
[TD="align: right"][/TD]
[TD="align: center"]DEBIT[/TD]
[TD="align: center"]CREDIT[/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #FFFFCC, align: center"]1130[/TD]
[TD="align: right"]0.00[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #FFFFCC, align: center"]1210[/TD]
[TD="align: right"]0.00[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #FFFFCC, align: center"]1205[/TD]
[TD="align: right"]0.00[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]11[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]12[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #FFFFCC, align: center"]4176[/TD]
[TD="align: right"]0.00[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]13[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]14[/TD]

[TD="align: right"][/TD]
[TD="bgcolor: #FFFFCC, align: center"]4166[/TD]
[TD="align: right"]0.00[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]15[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]16[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]17[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]18[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #FFFFCC, align: center"]5820[/TD]
[TD="align: right"]0.00[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]19[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]20[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]21[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]22[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #FFFFCC, align: center"]5580[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]23[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #FFFFCC, align: center"]1270[/TD]
[TD="align: right"]0.00[/TD]
[TD="align: right"]Daily Agency Settlment[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]24[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"]Debit[/TD]
[TD="align: center"]Credit[/TD]

[TD="align: center"]25[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]Bank 1130[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]26[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #FFFFCC, align: center"]5445[/TD]
[TD="align: right"]0.00[/TD]
[TD="align: right"]Interac 1205[/TD]
[TD="align: right"][/TD]
[TD="align: right"]0.00[/TD]

[TD="align: center"]27[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #FFFFFF, align: center"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]Credit 1210[/TD]
[TD="align: right"][/TD]
[TD="align: right"]0.00[/TD]

[TD="align: center"]28[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]Fuel 4022[/TD]
[TD="align: right"]0.00[/TD]
[TD="align: right"][/TD]

[TD="align: center"]29[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #FFFFFF, align: center"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]Gift Card[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]30[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #FFFFFF, align: center"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]C/c Dsc 5315[/TD]
[TD="align: right"]0.00[/TD]
[TD="align: right"][/TD]

[TD="align: center"]31[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #FFFFFF, align: center"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]32[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #FFFFCC, align: center"]1080[/TD]
[TD="align: right"]0.00[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]33[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #FFFFFF, align: center"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]34[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #FFFFFF, align: center"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]35[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #FFFFCC, align: center"]5300[/TD]
[TD="align: right"]0.00[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]36[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]37[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #FFFFCC, align: center"]5510[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]38[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]39[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]40[/TD]
[TD="bgcolor: #CCFFCC"]AGENCY FUEL SALES[/TD]
[TD="bgcolor: #CCFFCC, align: right"][/TD]
[TD="bgcolor: #CCFFCC, align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #FFFFCC, align: center"]4022[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #CCFFCC, align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]41[/TD]
[TD="bgcolor: #CCFFCC"]CONFECTIONARY SALES[/TD]
[TD="bgcolor: #CCFFCC, align: right"][/TD]
[TD="bgcolor: #CCFFCC, align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #FFFFCC, align: center"]4160[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #CCFFCC, align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]42[/TD]
[TD="bgcolor: #CCFFCC"]LOTTERY SALES[/TD]
[TD="bgcolor: #CCFFCC, align: right"][/TD]
[TD="bgcolor: #CCFFCC, align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #FFFFCC, align: center"]4170[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #CCFFCC, align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]43[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #FFFFCC, align: center"]4176[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]44[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #FFFFCC, align: center"]4165[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]45[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #FFFFCC, align: center"]2070[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]46[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]47[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"][/TD]
[TD="align: right"]0.00[/TD]
[TD="align: right"]0.00[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]48[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]49[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #FFFFCC, align: center"]5380[/TD]
[TD="align: right"]0.00[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Daily Template​

The objects highlighted in green are the cells I am interested in consolidating data from. The first one at the top is where I would type in the current date, but I think date information may be obtainable from the file name.

I am trying to achieve a consolidated worksheet that would look something like this:

Excel 2002

	A	B	C	D

<colgroup><col style="width: 25pxpx"><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: center"]October, 2013[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]2[/TD]
[TD="align: center"]Date[/TD]
[TD="bgcolor: #CCFFCC"]AGENCY FUEL SALES[/TD]
[TD="bgcolor: #CCFFCC"]CONFECTIONARY SALES[/TD]
[TD="bgcolor: #CCFFCC"]LOTTERY SALES[/TD]

[TD="align: center"]3[/TD]
[TD="bgcolor: #CCFFCC, align: center"]October 01[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]
[TD="bgcolor: #CCFFCC, align: center"]October 02[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]
[TD="bgcolor: #CCFFCC, align: center"]October 03[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]
[TD="bgcolor: #CCFFCC, align: center"]October 04[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]
[TD="bgcolor: #CCFFCC, align: center"]October 05[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]
[TD="bgcolor: #CCFFCC, align: center"]October 06[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]
[TD="bgcolor: #CCFFCC, align: center"]October 07[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]
[TD="bgcolor: #CCFFCC, align: center"]October 08[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]11[/TD]
[TD="bgcolor: #CCFFCC, align: center"]October 09[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]12[/TD]
[TD="bgcolor: #CCFFCC, align: center"]October 10[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]13[/TD]
[TD="bgcolor: #CCFFCC, align: center"]October 11[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]14[/TD]
[TD="bgcolor: #CCFFCC, align: center"]October 12[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]15[/TD]
[TD="bgcolor: #CCFFCC, align: center"]October 13[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]16[/TD]
[TD="bgcolor: #CCFFCC, align: center"]October 14[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]17[/TD]
[TD="bgcolor: #CCFFCC, align: center"]October 15[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]18[/TD]
[TD="bgcolor: #CCFFCC, align: center"]October 16[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]19[/TD]
[TD="bgcolor: #CCFFCC, align: center"]October 17[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]20[/TD]
[TD="bgcolor: #CCFFCC, align: center"]October 18[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]21[/TD]
[TD="bgcolor: #CCFFCC, align: center"]October 19[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]22[/TD]
[TD="bgcolor: #CCFFCC, align: center"]October 20[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]23[/TD]
[TD="bgcolor: #CCFFCC, align: center"]October 21[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]24[/TD]
[TD="bgcolor: #CCFFCC, align: center"]October 22[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]25[/TD]
[TD="bgcolor: #CCFFCC, align: center"]October 23[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]26[/TD]
[TD="bgcolor: #CCFFCC, align: center"]October 24[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]27[/TD]
[TD="bgcolor: #CCFFCC, align: center"]October 25[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]28[/TD]
[TD="bgcolor: #CCFFCC, align: center"]October 26[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]29[/TD]
[TD="bgcolor: #CCFFCC, align: center"]October 27[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]30[/TD]
[TD="bgcolor: #CCFFCC, align: center"]October 28[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]31[/TD]
[TD="bgcolor: #CCFFCC, align: center"]October 29[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]32[/TD]
[TD="bgcolor: #CCFFCC, align: center"]October 30[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]33[/TD]
[TD="bgcolor: #CCFFCC, align: center"]October 31[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Target Data Sheet​

One thing to note is that some sort of a paste-->tranpose function to get the items from being listed vertically to being listed horizontally (or vice versa (I'm dyslexic)) would be very nice, but not necessary if listing dates horizontally is easier. Doesn't matter to me, I am just looking to create some charts, and do some year to year comparisons. The latter will be more useful and convenient than the way I have been doing it now (opening multiple worksheets and comparing one by one).

I should also mention, that I use an organized folder structure and a simple naming convention. The files are named [mmm dd] and placed into [Month yyyy] Folders. For example October 2013 Folder would contain files Oct 1.xls,Oct 2.xls....Oct 31.xls

I was hoping to generate one consolidated month report by applying a macro to each month folder, and like I said earlier, the date could be grabbed from the file name itself.

Let me know if you think you guys can help,
I appreciate all of your time, and want thank you in advance,
Cheers!

 

[AlphaFrog]

There is a free add-in called RDB Merge that I think will get you 90% there. It doesn't transpose the data but it can colsoidate it side-by-side where each day would be a column each with three rows with your categories. After it consolidates the data, you could have a simple macro to transpose the result.

RDB Merge

Use the Paste data next to each other option.

 

[doublejtacoma]

Hey thanks so much AlphaFrog . It looks like that might work. I will try it tomorrow when I'm back at work. I can quickly transpose the date myself. Thanks again for the quick response. Great people make for a great forum!

 

[doublejtacoma]

I have had a chance to try it and it works, but with one limitation. So my files are listed by mmm dd.xls (Oct 1.xls-Oct 31.xls for example). For whatever reason RBDmerge, arranges them in that stupid illogical order of Oct 1, Oct 10, Oct 11,...Oct 19, Oct 2, Oct 20...Oct 29, Oct 3,etc. I cant seem to sort it either. I am stuck here with no way to sort it into a proper ascending order, ie. from 1st to 31st. I am thinking some sort of dating excel add-on might work because it is picking up the date as just text I guess. I can't change the format of the date cells.
Excel 2002

	A	B	C	D

<colgroup><col style="width: 25pxpx"><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: center"]September 1/13[/TD]
[TD="align: right"]44072.25[/TD]
[TD="align: right"]8342.12[/TD]
[TD="align: right"]444.00[/TD]

[TD="align: center"]2[/TD]
[TD="align: center"]September 10/13[/TD]
[TD="align: right"]23100.82[/TD]
[TD="align: right"]4799.06[/TD]
[TD="align: right"]397.00[/TD]

[TD="align: center"]3[/TD]
[TD="align: center"]September 11/13[/TD]
[TD="align: right"]25974.75[/TD]
[TD="align: right"]4959.43[/TD]
[TD="align: right"]473.00[/TD]

[TD="align: center"]4[/TD]
[TD="align: center"]September 13/13[/TD]
[TD="align: right"]24991.53[/TD]
[TD="align: right"]5403.42[/TD]
[TD="align: right"]398.00[/TD]

[TD="align: center"]5[/TD]
[TD="align: center"]September 13/13[/TD]
[TD="align: right"]24693.68[/TD]
[TD="align: right"]5458.68[/TD]
[TD="align: right"]845.00[/TD]

[TD="align: center"]6[/TD]
[TD="align: center"]September 14/13[/TD]
[TD="align: right"]29049.97[/TD]
[TD="align: right"]5864.68[/TD]
[TD="align: right"]664.00[/TD]

[TD="align: center"]7[/TD]
[TD="align: center"]September 15/13[/TD]
[TD="align: right"]26501.71[/TD]
[TD="align: right"]5690.43[/TD]
[TD="align: right"]353.00[/TD]

[TD="align: center"]8[/TD]
[TD="align: center"]September 16/13[/TD]
[TD="align: right"]24400.52[/TD]
[TD="align: right"]4477.71[/TD]
[TD="align: right"]343.00[/TD]

[TD="align: center"]9[/TD]
[TD="align: center"]September 17/13[/TD]
[TD="align: right"]22471.08[/TD]
[TD="align: right"]4357.88[/TD]
[TD="align: right"]331.00[/TD]

[TD="align: center"]10[/TD]
[TD="align: center"]September 18/13[/TD]
[TD="align: right"]22843.74[/TD]
[TD="align: right"]4819.61[/TD]
[TD="align: right"]1235.00[/TD]

[TD="align: center"]11[/TD]
[TD="align: center"]September 19/13[/TD]
[TD="align: right"]22493.40[/TD]
[TD="align: right"]4720.19[/TD]
[TD="align: right"]542.00[/TD]

[TD="align: center"]12[/TD]
[TD="align: center"]September 2/13[/TD]
[TD="align: right"]44308.80[/TD]
[TD="align: right"]6859.29[/TD]
[TD="align: right"]365.00[/TD]

[TD="align: center"]13[/TD]
[TD="align: center"]September 20/13[/TD]
[TD="align: right"]20905.22[/TD]
[TD="align: right"]5306.79[/TD]
[TD="align: right"]788.00[/TD]

[TD="align: center"]14[/TD]
[TD="align: center"]September 21/13[/TD]
[TD="align: right"]25207.21[/TD]
[TD="align: right"]4814.88[/TD]
[TD="align: right"]1065.00[/TD]

[TD="align: center"]15[/TD]
[TD="align: center"]September 22/13[/TD]
[TD="align: right"]37647.96[/TD]
[TD="align: right"]5391.23[/TD]
[TD="align: right"]789.00[/TD]

[TD="align: center"]16[/TD]
[TD="align: center"]September 23/13[/TD]
[TD="align: right"]25462.97[/TD]
[TD="align: right"]3881.87[/TD]
[TD="align: right"]242.00[/TD]

[TD="align: center"]17[/TD]
[TD="align: center"]September 24/13[/TD]
[TD="align: right"]19934.32[/TD]
[TD="align: right"]4296.22[/TD]
[TD="align: right"]267.00[/TD]

[TD="align: center"]18[/TD]
[TD="align: center"]September 25/13[/TD]
[TD="align: right"]22141.08[/TD]
[TD="align: right"]4603.88[/TD]
[TD="align: right"]805.00[/TD]

[TD="align: center"]19[/TD]
[TD="align: center"]September 26/13[/TD]
[TD="align: right"]20978.07[/TD]
[TD="align: right"]4587.15[/TD]
[TD="align: right"]569.00[/TD]

[TD="align: center"]20[/TD]
[TD="align: center"]September 27/13[/TD]
[TD="align: right"]26873.69[/TD]
[TD="align: right"]5323.50[/TD]
[TD="align: right"]962.00[/TD]

[TD="align: center"]21[/TD]
[TD="align: center"]September 28/13[/TD]
[TD="align: right"]27151.08[/TD]
[TD="align: right"]5089.46[/TD]
[TD="align: right"]922.00[/TD]

[TD="align: center"]22[/TD]
[TD="align: center"]September 29/13[/TD]
[TD="align: right"]33053.44[/TD]
[TD="align: right"]4863.99[/TD]
[TD="align: right"]446.00[/TD]

[TD="align: center"]23[/TD]
[TD="align: center"]September 3/13[/TD]
[TD="align: right"]27605.96[/TD]
[TD="align: right"]4963.93[/TD]
[TD="align: right"]568.00[/TD]

[TD="align: center"]24[/TD]
[TD="align: center"]September 30/13[/TD]
[TD="align: right"]23397.71[/TD]
[TD="align: right"]3729.02[/TD]
[TD="align: right"]501.00[/TD]

[TD="align: center"]25[/TD]
[TD="align: center"]September 4/13[/TD]
[TD="align: right"]26375.75[/TD]
[TD="align: right"]5414.27[/TD]
[TD="align: right"]623.00[/TD]

[TD="align: center"]26[/TD]
[TD="align: center"]September 5/13[/TD]
[TD="align: right"]26396.09[/TD]
[TD="align: right"]5218.01[/TD]
[TD="align: right"]917.00[/TD]

[TD="align: center"]27[/TD]
[TD="align: center"]September 6/13[/TD]
[TD="align: right"]27099.54[/TD]
[TD="align: right"]5022.44[/TD]
[TD="align: right"]877.00[/TD]

[TD="align: center"]28[/TD]
[TD="align: center"]September 7/13[/TD]
[TD="align: right"]26244.18[/TD]
[TD="align: right"]5846.26[/TD]
[TD="align: right"]811.00[/TD]

[TD="align: center"]29[/TD]
[TD="align: center"]September 8/13[/TD]
[TD="align: right"]27703.99[/TD]
[TD="align: right"]5312.78[/TD]
[TD="align: right"]1479.00[/TD]

[TD="align: center"]30[/TD]
[TD="align: center"]September 9/13[/TD]
[TD="align: right"]25099.22[/TD]
[TD="align: right"]4651.00[/TD]
[TD="align: right"]437.00[/TD]

</tbody>

Sheet1​

Ignore the fact that it says September when I am referring to October. Any way to sort this out??

 

[AlphaFrog]

• Select column A
• Ctrl+H to open the Replace dialog
  [*=1]Find what: /
  [*=1]Replace with: , 20
  [*=1]Replace All
• This will convert the text to serial dates.
• Select columns A:D and sort on column A

 

[doublejtacoma]

Thank you!!