Populate formulas in cells
Folks,
I have attached 2 workbooks. The first, "Consolidate" has a section where monthly totals from other monthly tabs are placed. The second file, "Append" is used to add tabs to "Consolidated" and all the daily detail is located in that workbook as well as the monthly total.
Here's how this works. Place these two files in any folder. Open up the workbook "Consolidated" and click of the command button "Add Year". Enter in "2014". You will then need to append to this workbook all the worksheets from "Append" and ask to enter in a 2 digit number (14 for this year) that will be added to the new appended worksheets for the year. For example Jan becomes Jan 14, Feb becomes Feb 14, and so on.
After the "Consolidated" worksheets has it's new tab I populated under each category, by month, a total from a month. For example, January would only show the totals for the month of Jan and so on.
I'm building a macro for each month to populate the consolidated monthly total by category.
The macros are labeled modUpdateJanuaryFormulas, etc. There has got to be an easier way to do this and that what I'm hoping for.
Append.xlsm
Consolidated.xlsm
I have attached 2 workbooks. The first, "Consolidate" has a section where monthly totals from other monthly tabs are placed. The second file, "Append" is used to add tabs to "Consolidated" and all the daily detail is located in that workbook as well as the monthly total.
Here's how this works. Place these two files in any folder. Open up the workbook "Consolidated" and click of the command button "Add Year". Enter in "2014". You will then need to append to this workbook all the worksheets from "Append" and ask to enter in a 2 digit number (14 for this year) that will be added to the new appended worksheets for the year. For example Jan becomes Jan 14, Feb becomes Feb 14, and so on.
After the "Consolidated" worksheets has it's new tab I populated under each category, by month, a total from a month. For example, January would only show the totals for the month of Jan and so on.
I'm building a macro for each month to populate the consolidated monthly total by category.
The macros are labeled modUpdateJanuaryFormulas, etc. There has got to be an easier way to do this and that what I'm hoping for.
Append.xlsm
Consolidated.xlsm
ASKER
I truly thought I explained how the two workbooks are related. The purpose for the "Append" is simply a Jan - Dec tabed labeled workbook with formulas. Those worksheet are added o the "Consolidated" workbook and the monthly tabs are modified my add the last 2 digits of the year that the user provides, Jan becomes Jan 14, Feb become Feb 14 and so on.
The last thing to happen is to tie the formulas for the totals for each month that was added to "Consolidated" (Jan 14, Feb 14, etc.) The way I'm doing it is through a series on modules that populates the range C6:U17 on the "Consolidated". It's the only way I know.
The reason for two files is that my user doesn't want to be changing the tab labels manually. Everything up to populating the formulas works well. Although populating the "Consolidate" formulas works the way I'm doing is "sloppy" so I'm seeking a better way. BTW, each "Consolidated" workbook is saved as a Project and they are tracking 15 projects at the time. The two files are not template files since I do not know how they want to manage these files.
Hope this helps.
The last thing to happen is to tie the formulas for the totals for each month that was added to "Consolidated" (Jan 14, Feb 14, etc.) The way I'm doing it is through a series on modules that populates the range C6:U17 on the "Consolidated". It's the only way I know.
The reason for two files is that my user doesn't want to be changing the tab labels manually. Everything up to populating the formulas works well. Although populating the "Consolidate" formulas works the way I'm doing is "sloppy" so I'm seeking a better way. BTW, each "Consolidated" workbook is saved as a Project and they are tracking 15 projects at the time. The two files are not template files since I do not know how they want to manage these files.
Hope this helps.
ok fine let me propose a more efficient better handling way.
My solution forsee 1 file !!! yes only 1 file that is the consolidated one that has already the Jan to Dec worksheets in there but are hidden. and when you activate the macro new year then it copies Jan to Jan 14 or whatever with the correct values and these new Jan 14, Feb 14 in consolidated are now visible.
Is that workable for you ?
Is yes then I still need you to answer something that is not clear when we create Jan 14 we copy row of Jan in Consolidated to the new sheet Jan 14 at what row ?? presume first row but you need to clarify this. Andd you have date there what should we put as a date ? Presume this is the opening for the year right ???
gowflow
My solution forsee 1 file !!! yes only 1 file that is the consolidated one that has already the Jan to Dec worksheets in there but are hidden. and when you activate the macro new year then it copies Jan to Jan 14 or whatever with the correct values and these new Jan 14, Feb 14 in consolidated are now visible.
Is that workable for you ?
Is yes then I still need you to answer something that is not clear when we create Jan 14 we copy row of Jan in Consolidated to the new sheet Jan 14 at what row ?? presume first row but you need to clarify this. Andd you have date there what should we put as a date ? Presume this is the opening for the year right ???
gowflow
ASKER
the client will be adding years, depending upon the length of the project. That's why we can add additional years to the Consolidated (although that has not been implemented yet).
Actually, there are modules for each month that reference the totals only of each monthly worksheet to the row/column in the Consolidated as the totals for each month. I believe you've got it backwards. It's from the monthly totals (monthly holds the detail) to the Consolidated.
Actually, there are modules for each month that reference the totals only of each monthly worksheet to the row/column in the Consolidated as the totals for each month. I believe you've got it backwards. It's from the monthly totals (monthly holds the detail) to the Consolidated.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks for the tip in ID: 40214448. Reminds me of Access and VB.
Yes, I use a variable in the modAddYear module. There is an input box for a four digit year to populate B6:B17.
The InsertNewYearConsolidated looks to work - it even picks up on the added tabs in the Design cells (you wrote that code). Adding cell labels is for another question since we're focusing on adding formulas.
Did I answer your question?
Yes, I use a variable in the modAddYear module. There is an input box for a four digit year to populate B6:B17.
The InsertNewYearConsolidated looks to work - it even picks up on the added tabs in the Design cells (you wrote that code). Adding cell labels is for another question since we're focusing on adding formulas.
Did I answer your question?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
How does this interface with the module "InsertNewYearConsolidated
It doesn't. You no longer have the button on the Consolidated sheet that called that module so I thought you weren't using it any more.
How does it get used?
What exactly do you want to do with formulas if it is called?
How does it get used?
What exactly do you want to do with formulas if it is called?
ASKER
If you ran the macro "InsertNewYearConsolidated
1. Labeling the new cell Design (they work when clicked on it, just no label)
2. The second year 2015 is missing in Column B for the added year
3. There are no formulas in the added year.
1. Labeling the new cell Design (they work when clicked on it, just no label)
2. The second year 2015 is missing in Column B for the added year
3. There are no formulas in the added year.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
let me make sure I understand. At the end of AddYear I would call the above module?
ASKER
How does "InsertNewYearConsolidated
No. At line 6 of my InsertNewYearConsolidated,
1. Open Consolidated
2. Run AddYear
3. Run InsertNewYearConsolidated
1. Open Consolidated
2. Run AddYear
3. Run InsertNewYearConsolidated
ASKER
Actually, Run InsertNewYearConsolidated stands on it own, only to run when a new year is added to the project. OK, I see AddYear now. I overlooked it earlier. Let me see what happens now.
Actually, Run InsertNewYearConsolidated stands on it own, only to run when a new year is added to the projectOK remove the AddYear line if you want to but if you leave it there you never have to run AddYear, only InsertNewYearConsolidated.
ASKER
Exactly and that's where the formulas, year and labels "Detail" will complete adding a new year command button in addition to Add Year (actually I need them to agree to change that label to add new project.)
ASKER
How did you get the "Detail" cells to do what they do? You've got me on that one????
Yes, InsertNewearConsolidate would be a stand alone.
If that module can get to work I would change the button "Add Year" to "Add Project" and the InsertNewearConsolidate module could be labeled "Add Another Year"
Yes, InsertNewearConsolidate would be a stand alone.
If that module can get to work I would change the button "Add Year" to "Add Project" and the InsertNewearConsolidate module could be labeled "Add Another Year"
ASKER
Are you waiting on me?
Nope just out with the girls (literally) getting some lunch.
How did you get the "Detail" cells to do what they do? You've got me on that one????
' Copy the cells that contain the 'Detail'
Range("V6:V17").Copy
' Select the rows (yes, I know)
Range("V18:V29").Select
With Selection
' Paste, copying the source formatting to the destination
.PasteSpecial Paste:=xlPasteAllUsingSourceTheme, Operation:=xlNone _
, SkipBlanks:=False, Transpose:=False
' Paste the values
.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
End WithASKER
where does this code go?
the "girls" sounds like grandkids
the "girls" sounds like grandkids
I was just adding comments to lines 26 - 33 in post ID: 40215074 in answer to your "How did..." question.
ASKER
OK...
I'm going to close this question at : 40215074
Here's the reason. They are asking so much because they don't want to cut and paste. I am so tired of this. They're going to get what's been done - game, set, and match. I can this going on and on. Enough is enough. This has no reflection on EE.
I'm going to close this question at : 40215074
Here's the reason. They are asking so much because they don't want to cut and paste. I am so tired of this. They're going to get what's been done - game, set, and match. I can this going on and on. Enough is enough. This has no reflection on EE.
ASKER
Thank you so very much
I know you worked on this as hard as you do on anything!
Have a great day - I appreciate you so much
I know you worked on this as hard as you do on anything!
Have a great day - I appreciate you so much
YW.
1) When is time to 'Append' a year what do you want to copy from where to where this is not clear to me although I see lot of code that can be summarize into couple of lines.
2) You need to create the new tabs Jan 14, Feb 14 etc.. in what file ? Consolidated or Append ?
3) If answer to 2) is in Consolidated so why then you have 2 files ? Are the monthly sheets a Template ? do you have data put in there ?
so many questions you need to maybe explain just the functioning then it would help understanding the whole concept of these 2 files.
gowflow