Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 119
  • Last Modified:

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
0
Frank Freese
Asked:
Frank Freese
  • 14
  • 9
  • 2
3 Solutions
 
gowflowCommented:
For sure there is a simpler way to do it but need to understand few things first:

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
0
 
Frank FreeseAuthor Commented:
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.
0
 
gowflowCommented:
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
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
Frank FreeseAuthor Commented:
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.
0
 
Martin LissRetired ProgrammerCommented:
OK first off when writing VBA code you should avoid using Select for two reasons. One is that selection takes time (although a very small amount) and the other is that your user could select something else while the macro was running and your code would operate on the wrong cell. So for example do

    Range("C6").FormulaR1C1 = "='Jan 14'!R[31]C"

Open in new window


rather than

    Range("C6").Select
    ActiveCell.FormulaR1C1 = "='Jan 14'!R[31]C"

Open in new window


Now back to your question. As has been suggested there are easier ways than the way you are doing it but you already have a start so one thing you could do would be add line 21 below, but that would just be useful for one specific year and it would be a relatively easy task to change the monthly macros so that they use a variable representing the year. Does such a variable already exist in the workbook? BTW I could search each of your one-macro-per-module modules but rather than do that somewhat tedious task it's easier to ask, and note that if all your macros were in one or two modules it would be easier for me or you to find out.

Sub InsertNewYearConsolidated()
    Dim ws As Worksheet, rw As Long
    Set ws = Worksheets("Consolidated")
    ws.Select
    
    'Insert 12 rows above Totals and the spacer row
    rw = ws.Range("A:A").Find("Totals").Row
    ws.Rows(((rw - 1) & ":" & (rw + 10))).Insert Shift:=xlDown
    
    'copy the format from the first 12 rows (6 to 17)
    ws.Range("A6:U17").Copy
    ws.Range("A" & (rw - 1)).Select
    Selection.PasteSpecial Paste:=xlPasteFormats
   Application.CutCopyMode = False
    Range("A6:A17").Select
    Selection.Copy
ws.Range("A6:A17").Copy
    ws.Range("A" & (rw - 1)).Select
    Selection.PasteSpecial Paste:=xlPasteValues
    Range("A1").Select
    UpdateFormulas
End Sub

Open in new window

0
 
Frank FreeseAuthor Commented:
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?
0
 
Martin LissRetired ProgrammerCommented:
Try this modification of AddYear.

Sub AddYear()

  ' add year
    Dim Yearly As String
    Dim lngIndex As Long
    Dim strYr As String
    
    Yearly = InputBox("Please enter a 4 digit year to append to the added rows")
    'Yearly = Trim(Yearly)
    Range("B6:B17").Select
    Selection.NumberFormat = "General"
    Selection.Value = Yearly
    strYr = " " & Right$(Yearly, 2)
    'Append worksheet
    Append
    ReturnMenu
'    UpdateFormulas
    For lngIndex = 1 To 12
        ' MonthName(lngIndex, True) returns "Jan" when lngIndex is 1
        Range("C" & lngIndex + 5).FormulaR1C1 = "='" & MonthName(lngIndex, True) & strYr & "'!R[31]C[-1]"
        Range("D" & lngIndex + 5).FormulaR1C1 = "='" & MonthName(lngIndex, True) & strYr & "'!R[31]C[-1]"
        Range("E" & lngIndex + 5).FormulaR1C1 = "='" & MonthName(lngIndex, True) & strYr & "'!R[31]C[-1]"
        Range("F" & lngIndex + 5).FormulaR1C1 = "='" & MonthName(lngIndex, True) & strYr & "'!R[31]C[-1]"
        Range("G" & lngIndex + 5).FormulaR1C1 = "='" & MonthName(lngIndex, True) & strYr & "'!R[31]C[-1]"
        Range("H" & lngIndex + 5).FormulaR1C1 = "='" & MonthName(lngIndex, True) & strYr & "'!R[31]C"
        Range("I" & lngIndex + 5).FormulaR1C1 = "='" & MonthName(lngIndex, True) & strYr & "'!R[31]C"
        Range("J" & lngIndex + 5).FormulaR1C1 = "='" & MonthName(lngIndex, True) & strYr & "'!R[31]C"
        
        Range("K" & lngIndex + 5).FormulaR1C1 = "='" & MonthName(lngIndex, True) & strYr & "'!R[31]C"
        Range("L" & lngIndex + 5).FormulaR1C1 = "='" & MonthName(lngIndex, True) & strYr & "'!R[31]C"
        Range("M" & lngIndex + 5).FormulaR1C1 = "='" & MonthName(lngIndex, True) & strYr & "'!R[31]C"
        Range("N" & lngIndex + 5).FormulaR1C1 = "='" & MonthName(lngIndex, True) & strYr & "'!R[31]C"
    
        Range("O" & lngIndex + 5).FormulaR1C1 = "='" & MonthName(lngIndex, True) & strYr & "'!R[31]C"
        Range("P" & lngIndex + 5).FormulaR1C1 = "='" & MonthName(lngIndex, True) & strYr & "'!R[31]C"
        Range("Q" & lngIndex + 5).FormulaR1C1 = "='" & MonthName(lngIndex, True) & strYr & "'!R[31]C"
        Range("R" & lngIndex + 5).FormulaR1C1 = "='" & MonthName(lngIndex, True) & strYr & "'!R[31]C"
    
        Range("S" & lngIndex + 5).FormulaR1C1 = "='" & MonthName(lngIndex, True) & strYr & "'!R[31]C"
        Range("T" & lngIndex + 5).FormulaR1C1 = "='" & MonthName(lngIndex, True) & strYr & "'!R[31]C"
    Next

    MsgBox "Completed"
    Range("A1").Select
    End Sub

Open in new window

0
 
Frank FreeseAuthor Commented:
How does this interface with the module "InsertNewYearConsolidated"?
0
 
Martin LissRetired ProgrammerCommented:
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?
0
 
Frank FreeseAuthor Commented:
If you ran the macro "InsertNewYearConsolidated" by itself after you add the first year it's was designed to add the second year below the first year. It lacked three things:
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.
0
 
Martin LissRetired ProgrammerCommented:
When I asked "How does it get used" I guess I should have also asked "Does it get called before or after you do AddYear". Not knowing that answer I went ahead and assumed that you would always do AddYear first and so I added a call to that sub at the top of this one. If that's not a correct assumption then remove that line. Note: please use my AddYear.
Sub InsertNewYearConsolidated()
    Dim ws As Worksheet, rw As Long
    Set ws = Worksheets("Consolidated")
    'new
    Application.EnableEvents = False
    AddYear

    ws.Select
    
    'Insert 12 rows above Totals and the spacer row
    rw = ws.Range("A:A").Find("Totals").Row
    ws.Rows(((rw - 1) & ":" & (rw + 10))).Insert Shift:=xlDown
    
    'copy the format from the first 12 rows (6 to 17)
    ws.Range("A6:U17").Copy
    ws.Range("A" & (rw - 1)).Select
    Selection.PasteSpecial Paste:=xlPasteFormats
    Application.CutCopyMode = False
    Range("A6:A17").Select
    Selection.Copy
    ws.Range("A6:A17").Copy
    ws.Range("A" & (rw - 1)).Select
    Selection.PasteSpecial Paste:=xlPasteValues
    
    'new
    Range("V6:V17").Copy
    Range("V18:V29").Select
    With Selection
        .PasteSpecial Paste:=xlPasteAllUsingSourceTheme, Operation:=xlNone _
            , SkipBlanks:=False, Transpose:=False
        .PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
    End With
    Application.EnableEvents = True
    
    Range("A1").Select
    
End Sub

Open in new window

0
 
Frank FreeseAuthor Commented:
let me make sure I understand. At the end of AddYear I would call the above module?
0
 
Frank FreeseAuthor Commented:
How does "InsertNewYearConsolidated" get used? Let say that the project goes into a second year. I've been ask to add the second year to the first so that Jan 14 - Dec 15 would be contiguous with the data for the two years, totaled. That's what  InsertNewYearConsolidated is being designed for. Make more sense?
0
 
Martin LissRetired ProgrammerCommented:
No. At line 6 of my InsertNewYearConsolidated, AddYear is called. So all you would do is to run my InsertNewYearConsolidated. If that doesn't make sense it's probably because I don't understand your workflow which I assume up to this point was
1.    Open Consolidated
2.    Run AddYear
3.    Run InsertNewYearConsolidated
0
 
Frank FreeseAuthor Commented:
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.
0
 
Martin LissRetired ProgrammerCommented:
Actually, Run InsertNewYearConsolidated stands on it own, only to run when a new year is added to the project
OK remove the AddYear line if you want to but if you leave it there you never have to run AddYear, only InsertNewYearConsolidated.
0
 
Frank FreeseAuthor Commented:
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.)
0
 
Frank FreeseAuthor Commented:
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"
0
 
Frank FreeseAuthor Commented:
Are you waiting on me?
0
 
Martin LissRetired ProgrammerCommented:
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 With

Open in new window

0
 
Frank FreeseAuthor Commented:
where does this code go?
the "girls" sounds like grandkids
0
 
Martin LissRetired ProgrammerCommented:
I was just adding comments to lines 26 - 33 in post ID: 40215074 in answer to your "How did..." question.
0
 
Frank FreeseAuthor Commented:
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.
0
 
Frank FreeseAuthor Commented:
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
0
 
Martin LissRetired ProgrammerCommented:
YW.
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 14
  • 9
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now