How to create multiple month to date summaries from multiple worksheets

I have a client that adds invoices. The way we add the invoices is by account. Each invoice goes to its own account worksheet page. The client wanted me to do a YTD summary page for all the accounts on one page. Here is the logic below that I coded to do the YTD summary. Problem I have now is that they want me to do a Month to date summary. But they want a seperate month to date summary for each account worksheet page. I was able to code the individual YTD summary but the month to date summary going from individual account pages to individual month-to-date summary pages is more complicated. This would be summarizing by object account and by month from the invoice date.

I included a small spreadsheet showing some parts of my worksheet. The spreadsheet has two different account sheets for 35507 and 35509, Control WS, YTD_Summary WS, and MTD_Summary_35507 WS. The month-to-date-summary is currently just a copy of the year-to-date summary worksheet but I changed the columns to relect the months instead of the different accounts.

Each account has its own "CC_" WS so all the data for say account 35507 will appear on the CC_35507 worksheet so if I want to summarize CC_35507 by month I would have to take the invoice month and extract the month from the invoice date. Right now I have the month portion of the invoice date sitting in column BK of each CC_Account worksheet. So for each "CC_" worksheet I want to summarize each object portion of the account by month and place the value in the appropriate column of the MTD_Summary_account spreadsheet.

Question: How do I code logic to take these multiple account pages and code a month to date summary so that each account generates its own month to date summary page. THis request is way over my head.

Here is logic I coded for the year to date summary page.

Please Login or Register  to view this content.

I have written code to summarize my multiple Cost_Center Invoice worksheets into individual MTD_Summary_XXXXX worksheets where XXXXX is the Cost_Center and MTD is Month-to-date. Here is the code. It is not very pretty but it works.

Please Login or Register  to view this content.

The first set of code I supplied explains how to summarize my multiple Cost Center Invoice worksheets into one summary worksheet and then summarize them by Cost Center and within Cost Center by my object and tax accounts.

The second set of code takes the multiple Cost Center Invoice worksheets and summarizes each Cost Center Invoice worksheet into its own individual month-to-date worksheet.

Both sets of code I supplied work fine. Each of these subroutines supplied is independent of each other.

To get the logic to write my CalcMTDSummary subroutine I drew a diagram on paper to identify what I wanted to do. The following is briefly what I needed to do.

1) Loop thru all the worksheets in the workbook only working with those starting with "CC_"
2) Loop thru columns I1 to BH1 on the current "CC_" worksheet
3) Loop thru Rows 2 to Row Last -1 on current "CC_" worksheet
4) Sum the amount in the current cell into one of 12 work buckets.
5) At the completion of the current column being processed write the 12 individual total buckets to the appropriate MTD_Summary_XXXXX worksheet.

That is more or less the logic I drew myself on paper then I filled in the details from there.

If you can think of anything to make it more efficient then feel free to drop me a line.

Unfortunately columns A and Column B are retyped on each worksheet. I am trying to find a way to only code the object portion of the account and the description once so I do not have to rekey the information on all the worksheets.

It would be nice if it would pull columns A & B from my control worksheet that way if I add new objects then they would get included and I would not have to modify all these summary worksheets.