Updating macros to insert totals on two different sheets

**BlondOIverBlue** · 10-19-2010, 09:56 AM

On each "EOD_Report - mm-dd-yyyy" sheet (created by the "EOD_ReportV5" macro), I am looking to add a field at the bottom called Day's Total Units: which is a sum of all the Total Units: fields of each tech.

Then, for the "Week Ending" summary sheet (created by the "Build_Summary" macro), I'm looking to add a Week's Total Units: field which is the sum of each Day's Total Units: on the "EOD_Report - mm-dd-yyyy" sheets.

Right now I have manual SUM formulas built but I'd rather have the macros do all the work automatically each time they are ran, including the spacing and cell formats. Attached is a sample sheet, macros included, of my vision.

**MarvinP** · 10-19-2010, 10:20 AM

Hi,
I'd use your VBA code and plug in a formula on each EOD sheet at the bottom of:

WorksheetFunction.SumIf(E1:E (bottom),"Total Units:",F1:F (bottom))

This Excel formula will add each day's Total Units at the bottom.

Then for the Week Ending totals, I'd search each EOD sheet for "Day's Total Units: and sum the numbers from just right of them.

It looks like you are very close and can write code, so I hope my suggestion makes sense.

BTW - you should start using Option Explicit above your code so you define each variable. It will solve many problems

in code before they happen. Honest!

**TMShucks** · 10-19-2010, 10:23 AM

One way:

Option Explicit

Sub EOW_Total()

Dim lLastRow As Long
Const iRowIncr As Integer = 3

lLastRow = Range("A" & Rows.Count).End(xlUp).Row
Range("D" & (lLastRow + iRowIncr)).Value = "Week's Total Units:"
Range("E" & (lLastRow + iRowIncr)).Formula = _
    "=sum(E2:E" & lLastRow & ")"


End Sub

Sub EOD_Total()

Dim lLastRow As Long
Const iRowIncr As Integer = 3

lLastRow = Range("A" & Rows.Count).End(xlUp).Row + 4

Range("E" & (lLastRow + iRowIncr)).Value = "Day's Total Units:"

Range("F" & (lLastRow + iRowIncr)).FormulaR1C1 = _
        "=SUMIF(R2C[-1]:R[-2]C[-1],""Total Units:"",R2C:R[-2]C)"

End Sub

Regards

**BlondOIverBlue** · 10-20-2010, 02:20 PM

Hi all,

First I must clarify: I did not code any of this. I also must stress that the people on this forum have been incredibly patient and helpful on this project. I am learning to code on-the-fly (no prior exposure to coding) and have been studying the code trying to teach myself what does what, when, and how. I am learning so much, I can't tell you how much I appreciate it.

@MarvinP: I tried following your suggestion, but I got frustrated because I seemed to have broke more than I fixed.

@TMShucks: Your code seemed to be more "idiot-friendly" and I was able to incorporate the "EOD_Total" code into my daily macro (EOD_ReportV6). However, I could not for the life of me get the EOW code to merge into my "Build_Summary" macro without getting syntax errors and other messages telling me I hosed something.

I tried all day yesterday, but couldn't do it. Is it possible to merge the code into an existing macro so I don't have to run two separate macros?

**MarvinP** · 10-20-2010, 03:10 PM

Hi BlondOlverBlue,

The macro behind your sheets is a moving target. It isn't the most complicated I've seen but requires a very specific sheet to start with and after running the macro changes the sheet around so you can't run it again.

The programmer who created it (it looks like Jarrett from the File Properties) has locked himself into a contract support possition for life. With each request for a new field, or the reports to look differently, takes a programmer to decipher the code and insert new code to implement the new requirements.

Your delima is whether to rehire Jarrett (or whomever wrote the code) or use features of Excel to give you the same information without needing it to look like he made it look.

I'd be trying to use pivot tables on the RAW data to see if you can give youself the same and even more information about what you need. Then as more questions arrise you may be able to use Excel and its features instead of needing a programmer.

See the below for a few examples.
http://en.wikipedia.org/wiki/Pivot_table
http://ulearnoffice.com/excel/pivot.htm

**BlondOIverBlue** · 10-20-2010, 03:19 PM

I figured this final piece was going to be more of a hassle than what it's worth; seems to be that way. Push comes to shove, I'll just manually do a quick SUM formula on the weekly sheet. 99.99% of the work is done via code, so this 00.01% manual intervention is not a deal breaker. Thank you for those links! I'm going to save them and read them to see if I can rework this down the road.

**TMShucks** · 10-20-2010, 06:54 PM

Hi BlondOlverBlue

I didn't (and haven't) looked too closely at your code. I made the assumption that you had one routine to create the End of Day Report and a second to produce the Week Ending Summary. Is that correct?
If it is, you should be able to include a call to th respective EOD_Total and EOW_Total as the last line in your subroutine.

The two routines were written as stand alone a) for testing and b) not to screw up any existing code. In theory, all they'll do is work out where the data is and chuck in a "caption" and a sum/sumif a few rows down (governed by the iRowIncr variable to try and make it flexible ... but also so I could test it without necessarily overwriting your totals).

If you want to persevere with this solution let me know. Right now, I can't even get the end of day total to tag on but it might just be that I don't know the end to end process or have the raw data.

Take care

Regards

**MarvinP** · 10-20-2010, 07:26 PM

Hi TMShucks,

Look at the past few threads started by BlondOlverBlue and find a file with RAW in it. This is what you need to start with. The macro slices and dices up the RAW workbook to the end result. In this thread s/he didn't include the starting point. The person who wrote the code was a looper. Get ready for CASE statements to total groups of columns. I regretfully read this code to change the start of work to 7am instead of 10am a few days ago. I think I accomplished the task but wanted to shoot the person who wrote the code.

Have fun putting lipstick on this code.