I am so grateful to all EE's that have stepped up helping with this project. I've been asked to "expand" it and some of the work has already been done.
Let me explain what happens first, then go into my objectives. There are two workbooks- "Consolidated" and "Append". When the user wished to track costs of a project they select the form command button labeled "Add Project" In doing this they will be asked to enter a four digit year. The four digit year populates B6:B17. Next the user needs to add new tabs to "Consolidated" for the year extrapolating month of the year from the four digit year first enter. If the enter 2014 that populates B6:B17 new tabs are added to the "Consolidate" worksheet. For example, Jan 14, Feb 14...Dec 14.
The last thing is that formulas for each month for each category are added to the "Consolidated" in the range C6:U17.
These formulas comes from the monthly total of each month for each category (B37:U37 would be for Jan 14) and are posted to the "Consolidated" sheet for Jan 2014 (C6:U6)
When the user wishes to add another year to the project (which is what the macro "InsertNewYearConsolidated
" is for and works some) three things need to happen.
1. If the first year was 2014 and they wanted to add 2015 they would need the first year to be a sub-total and subsequent years to be subtotal also. For example, 2014 would be subtotal, 2015 would be subtotaled. Let's say that this project only lasted two year. After the last year there would be a grand total for the project cost. Now if they have only one year no subtotaling would be required.
2. Notice that in B6:B17 is the four year. In this example use 2014 when you click on "Add Project". If a second year was to be tracked then there there's a need to have the new year would need read 2015.
3. Finally, in the first year formulas are placed in C6:U17. In the next year formulas would be needed in the new year.
Here's my first objective. I'll follow-up with the other two objective is separate questions.
2. When the user select "Add Year" could we not simply add one to the year already in B6:B17. For example B6 = 2014 then the next year what ever the cell is would read 2015 and so for the for each of the 12 months.
Please keep in mind that the user may have a 1, 2, 3, 4, 5 year project. I'm trying to avoid a little user input as possible.
I've attached the files needed.