Need an excel procedure to replace static totals with actual formulas

Will the words "Total", "Revenue" and "Backlog" be in the Total line or will they need to be inserted?

The words total, revenue and backlog will appear in every row.

Meaning what you see in the sample is what I get, nothing has been manually inserted into the sample file.

Try this macro.

Sub Totals()
Dim lngLastRow As Long
Dim lngRow As Long
Dim curRevenue As Currency
Dim curBackLog As Currency

lngLastRow = Range("A1048576").End(xlUp).Row

For lngRow = 2 To lngLastRow
    If Cells(lngRow, 1) = "Total" Then
        Cells(lngRow, 3) = curRevenue
        lngRow = lngRow + 1
        Cells(lngRow, 3) = curBackLog
        curRevenue = 0
        curBackLog = 0
    Else
        If Cells(lngRow, 2) = "Revenue" Then
            curRevenue = curRevenue + Cells(lngRow, 3)
        Else
            curBackLog = curBackLog + Cells(lngRow, 3)
        End If
    End If
Next
End Sub

Select allOpen in new window

I tried that macro and it ran without any errors, but I still have the same static totals where I need formulas.

What is the mechanism that generates the un-totaled data? In other words, when do you want the totals updated?

We have an app called JReport by JinfoNet software. It creates the report and that is what we export to Excel. Their software gurus tell me the export cannot export the report with formulas intact.

What I attached is a way of doing it without formulas. Any time you get new data you can just run the macro. It could also be automated by triggering it when any of the dollar amounts are changed, by pressing a button, or in any one of several other ways.

I see, yes, this would work with a button. So, the sample file only contained a JAN column, the actual report will contain the current 12 month calendar, plus 12 months in the past and 11 months in the future. And prob 2,500 rows. What would the macro have to look like to accommodate that need?
thx....Bob

So ignoring the fact that I copied and pasted the data would it look like this (with more months)?

exactly

And in addition, the backlog numbers and totals will never be touched. They can remain static for reference only, just the revenue numbers will change

Basically I give this sheet out to the sales guys, they use the backlog as a reference and they forecast sales numbers in the revenue rows. Then they give it back to me for SQL loading.

OK, I've got to go out and I'll get back to you in a few hours.

OK, please don't forget about me :-) I am very excited about this solution and greatly appreciate your help!!!
thanks...Bob

Here you go. You can drag the button (actually a shape) anyplace you want it.
Q-28638064.xlsm

Great...so this macro doesn't care how many columns or rows I have? it will always update the revenue rows no matter?
thx

One more question Martin...how would I set up the macro to run anytime a revenue dollar amount is changed?
thx...Bob

so this macro doesn't care how many columns or rows I have? it will always update the revenue rows no matter?

That's correct.

how would I set up the macro to run anytime a revenue dollar amount is changed?

Add this code to the sheet. It's a little crude in that it recalculates all the revenue totals when one is changed.

Private Sub Worksheet_Change(ByVal Target As Range)

    If Not Intersect(Target, Range(UsedRange.Address)) Is Nothing Then
        If Cells(Target.Row, 2) = "Revenue" Then
            Application.EnableEvents = False
                RevenueTotals
            Application.EnableEvents = True
        End If
    End If
            
End Sub

Select allOpen in new window

I added this code right under the other code, not part of the other macro, it is seen as a new macro...but when I change a revenue value nothing happens. Did I plop it in the wrong place?
thx...Bob

In the workbook I posted, the RevenueTotals macro is in a code module (Module1). The new code should be in the sheet. Go to Visual Basics and double-click on Sheet1 which will open up the Code area for the sheet. Paste all 11 lines of the Worksheet_Change sub there.

OK, I am an idiot....here is the file with the pasted code, I know I am doing something wrong.
thanks so much
28638064.xlsm

No you have it correctly placed. If you change for example cell E6 to 1000 and then select any other cell, cell E10 will change to 11,000. In any case here's an update where just the section that contains the changed revenue cell is updated.
Q-28638064b.xlsm

I wonder if an Excel setting is wrong on my excel, because I downloaded your update, changed cell E2 from $8,000.00 to $5,000.00 and then clicked into cell E6 and the total in cell E4 stays at $8,000.00, never changes to $5,000.00 until I click the button. Sorry to be such a bother, I just want to get this right cause its genius!!!!
thx...Bob

I think I see what happened. There's a line of code that says Application.EnableEvents = False and one further along that says Application.EnableEvents = True. The value of EnableEvents determines if the code behind all events (like WorksheetChange) is executed or not, so what I assume happened is that it got set to False and some error occurred before it got set back to True. You can fix this by going to Visual Basic and bringing up the Immediate Window via ctrl+g. In that window type Application.EnableEvents = True and press return. Let me know if that fixes the problem.

Yes...that did it. Last question...I will need to copy/paste the magic that makes this work into each report export to excel I do. I am not sure what I need to copy/paste. When I do Alt + F8 I see a macro called revenuetotals, but if I type alt+F11 I see other code in several VB Projects, sheet1, immediate, and other things. Not being a VBA guy, I have no idea what is what, or what to copy/paste to my other spreadsheets.
thx again...Bob

When you say "each report export" are you talking about other workbooks? If so each one of them needs to have two things:

1.

The RevenueTotals macro in a code module (like Module1), and

2.

the Worksheet_Change code in whatever sheet contains the revenue figures. Note that if the other workbook already has code in that event, you can probably just paste my code into the existing sub at the top. If you like, make the changes to one of the other workbooks and attach it here or email it to me (you'll find my email address in my profile) so I can check your work.

OK, I will do this tomorrow Martin. Each month I will do an excel export from JReport and I will need to copy/paste the above 2 listed pieces of code to make the totals work. I will do a fresh extract tomorrow, and copy/paste the 2 things in and see if it works. I will also attach it here for your review.
thx martin...have a good night.
-Bob

Good morning Martin...Problem...I extracted the exact report to excel I will export each month, I followed your 2 step copy/paste process into this report and nothing works. Disclaimer...the file we were working with yesterday was a mock-up of the actual report, I suspect this is the issue? Attached is the exact report, but missing 500 rows of data, but the few rows I show are exactly how the report will look. What did I do wrong?
thx...Bob
ActualReport.xlsm

The problem is that the ActualReport has the data in different columns then the mock-up and when you need to look at data in specific places that kind of difference is very important. I can fix it up for you, no problem, but let me ask you three things:
1) Will every workbook have the same first four columns?
2) You included my original "crude" version of the WorksheetChange code which updates all the revenue totals when any one of them is changed, rather than the version in my Q_28638064b workbook which updates only the total associated with the changed revenue. Which version do you want?
3) I don't see the button I added. Do you want it?

I just noticed the "Total" column (Q). I assume you want that updated to reflect any change you make to one of the detail revenue numbers. True?

Answers...
1. Yes, every export will have the same first 4 columns.
2. I want the version that does the macro anytime a revenue cell changes, no button needed.
3. No button
4. Yes, the extract totals to the right in Q, I can easily add that formula and drag it. The more difficult one is the GRAND total at the bottom for revenue (we don't ever care about backlog), anyway to do that grand total?

Again Martin, I know what you have done thus far is WAY above and beyond, and I really do appreciate it. I wish I could award you 100,000 points....
-Bob

Thanks, I'll have it for you sometime today.

Thanks Martin...I look forward to combing through this tomorrow. I will let you know how it all goes, and I do appreciate your efforts, and putting everything into 1 event sure helps my copy and pasting...and comments are most helpful. I was stepping through the code today while watching the spreadsheet trying to learn what was going on.
Have a good evening...Bob

Have you seen my article on debugging? Some things it talks about like Watch expressions can help you as you step through the code.

I extracted a new report, copy/pasted the sheet code into the sheet and it works almost perfectly Martin. The only thing it's not doing right is the GRAND total at the bottom. Here is what is happening:
Customer Material No      Revenue
        1           12345                  5.00
                     TOTAL                 5.00


GRAND                                    10.00

It is adding up the column revenue amounts + the column sub total amounts, it should only be adding up the sub total amounts, can't double dip even if it looks like we sell more than we actually do :-)
thx Martin....Bob

Sorry. In the Worksheet_Change  event, near the bottom you'll find these two lines

                    If Cells(lngRow, REVENUE_COL) = "Revenue" Then
                        ' Sum the revenue values. Val() returns zero when the value is a space.

Select allOpen in new window

Change line 1 as shown

                    If Cells(lngRow, REVENUE_COL) = "Revenue" And Cells(lngRow, MATERIAL_COL) = "Total" Then
                        ' Sum the revenue values. Val() returns zero when the value is a space.

Select allOpen in new window

OK, that worked....but there is one more slice to this pie...if you scroll down through the file, you will see the names of 4 sales people in column A. Right now we only have 4 sales folks, but we may hire more. Anyway, when this file is exported from JREPORT to Excel, I will slice and dice the excel file into 4 separate files called salesperson1.xlsm, salesperson2.xlsm, etc. Then email it to those folks and they will fill in their forecast by month on the revenue lines, and only use the backlog numbers as a reference. When I get all 4 excel files back, I will append them into 1, make a few changes to get the file ready for SQL, then import it into SQL. So,,,,after I slice the report into 4, I need the grand totals to work at the bottom. I took a file with your code, which is working perfect...I delete all the rows after salesman CANAVAN, I do not delete the grey grand total footer, but when I change a revenue number the grand total in grey does not change. For some reason once I delete a bunch of rows the grey grand total at the bottom stops working. You will also notice that there is a sub-total after each salesperson change, which does not work, but its a moot point if I am going to slice up the report into 4 files.
thx Martin...we are "almost" there....Bob

So before we go any further let me see if I understand the process.

1. Export from jReport to Excel

You export the file from jReport to Excel. Does it have a grand total line at the bottom at that point even if there revenue totals my not be there?

2. Send a sheet to each sales person

You mail each salesperson their part of the sheet. On the current sheet there are 4 sections in col A that say "Canavan". Does he/she get 4 sections or just one and if just one, why are there 4 sections. Do the Totals in rows 231 and 232 go with it. Do headings like rows 1 and 2 go into each salesperson's sheet?

3. Recombine and modify

When you get them back you make some changes for SQL. Can you give an example of what they are?

1. JREPORT reads my 3 SQL tables (actual, backlog and forecast) using J2E Java technology and allows me to export the results to excel. See attached file JREPORT EXTRACT. Grand totals is in the report footer. All data exports to excel statically. That grand total is moot though as soon as I slice up the report by sales person.

2. That salesperson break is a JReport thing, Canavan gets ALL his customers despite any breaks JReport puts in. All headings go with each file to the respective salesperson. See attached sliced up files for the 4 sales people. I use their sub total footer line as their grand total. We might soon have 5 files if we hire another sales person.

3. The sales people look at their reports, which tells them what we have sold and invoiced thus far (actual revenue), what we have in backlog (orders taken but not started) and the forecast revenue. These examples do not show any forecast numbers yet because we do not have a 2015 forecast in yet, we need to get these 4 files working before sales people can add their forecasts.

Once I get all 4 files back, I remove all total and sub total lines, as well as the backlog rows, leaving me with a file only showing forecast numbers. I append the 4 files together, and add a column for forecast version, and its ready for SQL load. I need the forecast version because in any given year we might have loaded 4 different forecasts. JReport allows me to run the report and prompts me for which version of the forecast to use.

Hope this helps Martin and again, THANK YOU!!
greschner-file.xlsx
fasino-file.xlsx
cooke-file.xlsx
canavan-file.xlsx
JREPORT-EXTRACT.xlsx

Martin has provided me by far the BEST service I have ever encountered. MANY thanks for his diligence and loyalty to my project.

Thank you for the kind words and I'm glad I was able to help.

In my profile you'll find links to some articles I've written that may interest you.
Marty - MVP 2009 to 2014