Solved

Populate formulas in cells

Posted on 2014-07-21
25
106 Views
Last Modified: 2014-07-23
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
Comment
Question by:Frank Freese
  • 14
  • 9
  • 2
25 Comments
 
LVL 29

Expert Comment

by:gowflow
Comment Utility
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
 

Author Comment

by:Frank Freese
Comment Utility
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
 
LVL 29

Expert Comment

by:gowflow
Comment Utility
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
 

Author Comment

by:Frank Freese
Comment Utility
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
 
LVL 45

Assisted Solution

by:Martin Liss
Martin Liss earned 500 total points
Comment Utility
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
 

Author Comment

by:Frank Freese
Comment Utility
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
 
LVL 45

Accepted Solution

by:
Martin Liss earned 500 total points
Comment Utility
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
 

Author Comment

by:Frank Freese
Comment Utility
How does this interface with the module "InsertNewYearConsolidated"?
0
 
LVL 45

Expert Comment

by:Martin Liss
Comment Utility
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
 

Author Comment

by:Frank Freese
Comment Utility
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
 
LVL 45

Assisted Solution

by:Martin Liss
Martin Liss earned 500 total points
Comment Utility
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
 

Author Comment

by:Frank Freese
Comment Utility
let me make sure I understand. At the end of AddYear I would call the above module?
0
Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 

Author Comment

by:Frank Freese
Comment Utility
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
 
LVL 45

Expert Comment

by:Martin Liss
Comment Utility
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
 

Author Comment

by:Frank Freese
Comment Utility
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
 
LVL 45

Expert Comment

by:Martin Liss
Comment Utility
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
 

Author Comment

by:Frank Freese
Comment Utility
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
 

Author Comment

by:Frank Freese
Comment Utility
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
 

Author Comment

by:Frank Freese
Comment Utility
Are you waiting on me?
0
 
LVL 45

Expert Comment

by:Martin Liss
Comment Utility
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
 

Author Comment

by:Frank Freese
Comment Utility
where does this code go?
the "girls" sounds like grandkids
0
 
LVL 45

Expert Comment

by:Martin Liss
Comment Utility
I was just adding comments to lines 26 - 33 in post ID: 40215074 in answer to your "How did..." question.
0
 

Author Comment

by:Frank Freese
Comment Utility
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
 

Author Closing Comment

by:Frank Freese
Comment Utility
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
 
LVL 45

Expert Comment

by:Martin Liss
Comment Utility
YW.
0

Featured Post

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

A2 = A1 That kind of cell reference is relative.  If you copy it from A2 to B2, then B2 will get this: B2 = B1 That's all fine and good, but if you then insert a new row above row 2, you'll find: A3 = A1 B3 = B1 This is intentional. …
A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now