Summing numbers from multiple sheets

This code will create a sheet with your total. Let me know if you need help creating a button

Sub adgs()
Dim sht As Worksheet, dbLoadFee As Double


For Each sht In ActiveWorkbook.Worksheets
    If Left(sht.Name, 2) <> "MC" Then
        dbLoadFee = dbLoadFee + Application.WorksheetFunction.Sum(sht.Range("L2:L" & Rows.Count))
    End If
Next
    
Sheets.Add
ActiveSheet.Cells(1, 1) = "Total loading fees"
ActiveSheet.Cells(1, 2) = dbLoadFee
    
End Sub

Select allOpen in new window

Thomas

Error produced: Run time error 1004: Unable to get the Sum property of the Worksheet function class.

Just to be clear: it only tabulates the sum for the month prior or someway to tabulate for the month I want. Most of the time I will want the totals for the month prior as will seek the total for March in April, and for April, in May and so on.

And the monthly totals are determined by the dates in Col K: Date to load

Also to note, some sheets have no entry except for the column heading, in Col L. Not all sheets have a loading fee yet.

JaseS, it's not clear from which column you're taking the months, it seems like it could be column G or K.

I am writing a code that will check only column K. BUT there are some cases where column K is empty, those values will not be considered. Your work will change from preparing those numbers, to making sure that column K is well populated.

With that said, give me 10 minutes.

I guess the thing to check is if K has a value of the month and if so, it will have a value in L. Col G has nothing to do with the calculation

ok just one question...

is it ok if the final report provides the values for every month in separate rows? like:

january / 2011: 50000
february / 2011: 3000
march / 2011: 1533
.
.
.

sure, although once I get the totals for the month, I don't need to run them again, but it's okay

ok... I'll see what's easier to develop !
give me half hour ! :-)

here you go.
This code is simple, it takes into consideration the rules that you pointed in your question.
The attached file contains the code, but there's no button to run it.
to use it you have to open the file, go to the VBA by pressing Alt+F11, make sure that Excel has your file as the active workbook, and from withing the main subroutine, hit F5.

Please let me know in case youhave any doubts.

Sub SumUpTheValues()
Dim wbk                                 As Workbook
Dim sht                                 As Worksheet
Dim rngRandom                           As Range
Dim arrMonthsValues(1 To 12, 1 To 2)    As Variant
Dim i                                   As Integer
Dim lngColumnHeader                     As Long
Dim strArrayFormula                     As String

    Set wbk = ActiveWorkbook
    
    For i = 1 To 12
        arrMonthsValues(i, 1) = "'" & VBA.Format(VBA.DateSerial(Year(Now), i, 1), "MMM/YY")
    Next
    
    For Each sht In wbk.Worksheets
        If VBA.Left(sht.Name, 2) <> "MC" Then
            lngColumnHeader = fnColumnNumber(sht, "Load Fee")
            If lngColumnHeader <> 0 Then
                Set rngRandom = fnDetermineRandomRange(sht)
                If rngRandom Is Nothing Then
                    Exit Sub
                Else
                    For i = 1 To 12
                        strArrayFormula = "=SUM(NOT((ISBLANK(K2:K" & rngRandom.Row & ")))*(MONTH(K2:K" & rngRandom.Row & ")=" & i & ")*L2:L" & rngRandom.Row & ")"
                        If Not rngRandom Is Nothing Then rngRandom.FormulaArray = strArrayFormula
                          
                        arrMonthsValues(i, 2) = arrMonthsValues(i, 2) + rngRandom.Value
                        
                    Next
                
                End If
                Set rngRandom = Nothing
            End If
            
        End If
    Next
    
    Call DeleteFinalReport(wbk)
    Set sht = wbk.Sheets.Add(Before:=wbk.Sheets(1))
    With sht
        .Range("A1").Value = "MONTH"
        .Range("B1").Value = "VALUE"
        .Range("A1:B1").Font.Bold = True
        .Range(.Cells(2, 1), .Cells(13, 2)).Value = arrMonthsValues
        .Range("B:B").NumberFormat = "#,##0.00"
        .Name = "Final Report"
    End With
    Set sht = Nothing
    Set wbk = Nothing
    
End Sub

Private Function fnColumnNumber(sht As Worksheet, strColumnHeader As String) As Long
On Error Resume Next
    fnColumnNumber = Application.WorksheetFunction.Match(strColumnHeader, sht.Rows(1), 0)
End Function

Private Function fnDetermineRandomRange(sht As Worksheet) As Range
On Error Resume Next
    Set fnDetermineRandomRange = sht.UsedRange.SpecialCells(xlCellTypeLastCell).Offset(1, 0).Cells(1)
End Function

Private Sub DeleteFinalReport(wbk As Workbook)
On Error Resume Next
    With Application
        .DisplayAlerts = False
        wbk.Sheets("Final Report").Delete
        .DisplayAlerts = True
    End With
End Sub

Select allOpen in new window

Add-Monthly-Fees.xls

ok, you're losing me here.
I don't know what you mean by
"to use it you have to open the file, go to the VBA by pressing Alt+F11, make sure that Excel has your file as the active workbook, and from withing the main subroutine, hit F5."

Can't I create a button and insert the code behind it? And if so, where, what page do I put the button?

Hey Jases... I realized some flaws in the code above, so I improved the example.
Code and file with a button is attached, to make it easier for you.

p.s.: This code will execute similar to SUMIF for each month, only if the sheet does NOT start with MC and if it has a column names Load Fee...

to use it, make sure you have only two workbooks opened, the one attached in this post and the one with all the 100 sheets.

Then click the button and wait 2 seconds.

:) Enjoy !

Sub SumUpTheValues()
Dim wbkEach                             As Workbook
Dim wbk                                 As Workbook
Dim sht                                 As Worksheet
Dim rngRandom                           As Range
Dim arrMonthsValues(1 To 12, 1 To 2)    As Variant
Dim i                                   As Integer
Dim lngColumnHeader                     As Long
Dim strArrayFormula                     As String

    If Application.Workbooks.Count <> 2 Then
        MsgBox "This code was designed to run with only two workbooks open." & vbCrLf & "Please close all other workbooks.", _
               vbCritical + vbOKOnly, _
               "Error"
        Exit Sub
    End If
    
    For Each wbkEach In Application.Workbooks
        If wbkEach.Name <> ThisWorkbook.Name And Not wbkEach.IsAddin Then
            Set wbk = wbkEach
        End If
    Next wbkEach
    
    If wbk Is Nothing Then Exit Sub
    wbk.Activate
    For i = 1 To 12
        arrMonthsValues(i, 1) = "'" & VBA.Format(VBA.DateSerial(Year(Now), i, 1), "MMM/YY")
    Next
    
    For Each sht In wbk.Worksheets
        If VBA.Left(sht.Name, 2) <> "MC" Then
            lngColumnHeader = fnColumnNumber(sht, "Load Fee")
            If lngColumnHeader <> 0 Then
                Set rngRandom = fnDetermineRandomRange(sht)
                If rngRandom Is Nothing Then
                    Exit Sub
                Else
                    For i = 1 To 12
                        strArrayFormula = "=SUM(NOT((ISBLANK(K2:K" & rngRandom.Row & ")))*(MONTH(K2:K" & rngRandom.Row & ")=" & i & ")*L2:L" & rngRandom.Row & ")"
                        If Not rngRandom Is Nothing Then rngRandom.FormulaArray = strArrayFormula
                          
                        arrMonthsValues(i, 2) = arrMonthsValues(i, 2) + rngRandom.Value
                        
                    Next
                
                End If
                rngRandom.Clear
                Set rngRandom = Nothing
            End If
            
        End If
    Next
    
    Call DeleteFinalReport(wbk)
    Set sht = wbk.Sheets.Add(Before:=wbk.Sheets(1))
    With sht
        .Range("A1").Value = "MONTH"
        .Range("B1").Value = "VALUE"
        .Range("A1:B1").Font.Bold = True
        .Range(.Cells(2, 1), .Cells(13, 2)).Value = arrMonthsValues
        .Range("B:B").NumberFormat = "#,##0.00"
        On Error Resume Next
        .Name = "Final Report"
    End With
    Set sht = Nothing
    Set wbk = Nothing
    
End Sub

Private Function fnColumnNumber(sht As Worksheet, strColumnHeader As String) As Long
On Error Resume Next
    fnColumnNumber = Application.WorksheetFunction.Match(strColumnHeader, sht.Rows(1), 0)
End Function

Private Function fnDetermineRandomRange(sht As Worksheet) As Range
On Error Resume Next
    Set fnDetermineRandomRange = sht.UsedRange.SpecialCells(xlCellTypeLastCell).Offset(1, 0).Cells(1)
End Function

Private Sub DeleteFinalReport(wbk As Workbook)
On Error Resume Next
    If wbk.Worksheets.Count > 1 Then
        With Application
            .DisplayAlerts = False
            wbk.Sheets("Final Report").Delete
            .DisplayAlerts = True
        End With
    End If
End Sub

Select allOpen in new window

SumUpLoadFees.xls

gives an error: run time error 13

highlighted in yellow:
   arrMonthsValues(i, 2) = arrMonthsValues(i, 2) + rngRandom.Value

when I click the button, it immediately brings Mastercard.xls up front on the screen and then after about 15 secs, produces the error. Would it give an error like that if something was in Col L not an amount?

yes... most likely... if there's something that is not an amount and/or if there's something on columm K that is not a date...

I'll do a quick check  - of over 100 pages :{

no, dont do it...
It's just that, you never mentioned this possibility, so I didnt code considering it.
Give me 15 minutes.

:-)

and does the amount in Col L have to be directly across, next to, a date in Col K?

i dont get your question, but...
if there's nothing in column K, then column L's number will not sum up !
it only sums by looking at column K and getting the month from it...

what I could try to do, programatically always populate the empty cells with a date of the month from above it or below it, but i think it's kinda risky though...

i changed the one line of code in this one, to consider that text can be written in column L...

please let me know how to go about the empty cells on column K...

Should I consider the month from above, or the month from below ??
I would guess a logic here:
if the closest cell above with content has DAY below 15, I consider the same month.
if the closest cell above with content has DAY above 15, I consider the next month...

i honestly dont like this, and i think it's risky because this means the code playing with numbers likely putting them in wrong months.... but it's up to you... in case you decide not to go this path, here is the new file/code with the button. :-)
SumUpLoadFees.xls

Taking a look at the attached image you can see that the loading fees are not aligned with any date. This is a typical spreadsheet where I need to gather the loading fees for the month and sum them with other sheets of the same layout.

i understand... but see, the human factor (you) decides to which month these unrelated data goes...
I need you to explicitly open that logic that goes on in your brain when making this decision, because see, the code will do everything we tell it to do... SO we have to tell it something...

The final question is:
what should the code do with values on column L that are not related to any date on column K ?

okay, then what needs to happen is another code (another project) that populates Col K with today's date when an amount is input in Col i. That would put a date in Col K directly next to the value in Col L and that same code needs to calculate the loading fee in Col L and insert it.

ok that's it, we're getting somewhere :-)

So now please provide an example.
In the attached image, I see the first value that K is empty, we have:
97.32 in column L and
4893.08 in column I.
Do you mean that we'll have today's date on column K and the value on column L will change ?
what means "calculate it and insert it" ?

Let's go step by step... in that example that mentioned above, if someone (likely you) enters a value on column i, (i understand then that this new code will be triggered by changing a cell in column I, and not by the button, right ?) what should happen with K and L ?

dude, I'm tired...
let's do one thing...

I will go home now, and give you more time to think .... and then, tomorrow (or at some point during the weekend) I will come back here and check your reply and adapt the code (or create a new one) with the new logic for column K... sounds good ?

I really do need to go... ttyl !

Fernando

Here's how it works:

1. I enter an amount in Col F
2. I drag down a formula contained in Col i which kicks off a code
3. that code breaks up the 4893.08 as you see it in Col J, but skips a row when it does
4  it also puts a date next to it in Col K and does so until the entire 4893.08 is used up

What I am needing I see is another function that when I put a value in Col F it does the above and also
populates Col K with today's date and in Col L the Loading fee. Now, I already have the code that does steps 1-4 as detailed above.

So I guess what I need is the code that populates Col K on the same row where the 4,893.08 is with today's date and on the same row calculates the loading fee (which is 1.85% of Col F) and inserts that in Col L.

If you wanted to create this code I will start a related question, you reply and I'll give you the points.

HOWEVER, that does not take care of the current problem of how do we tie the date in Col K to the Loading Fee in Col L. So, what I can do, is go through my sheets and manually put a date in Col K on the same row as the Loading Fee in Col L, that way all loading fees in Col L will have a date right next to it on the same row, in Col K

Does this help?

also, keep in mind, that I will have a number of workbooks open so I'm not sure having the code work with having an 'active workbook open' will work as I always have more than one open.

I'll have to agree that its a new project therefore new question !
I'll change the active workbook thing then this question is done !
in the new question, it will make it easier if u put the code u have in it, then i can adapt it to do the remaining stuff, then you'll have a complete solution for all your problems :-)

when you post the code to finish up this question I'll post the related question immediately

sounds like a plan ! tomorrow !

Jases
sorry it took me a while to get into this today, but I did the best I could to keep my word.
Here's the new file with all the code you need, and changed way of working with multiple workbooks.

1) if there's only one workbook open (it is the one with the button), the code will show the Open Workbook dialog box
2) If there's two workbooks open, it will understand that the one which is not the one with the code, is the one supposed to run the sum
3) If there are multiple workbooks opened (more likely to happen), it will show a dialog box developed in VBA, to help you pick the one you want. It can be selected from the list box then press ok, or double-clicked...

Hope you find it sweet :-)

now I have to go drink some beer ! take care !
SumUpLoadFees.xls

gosh! I was waiting for an email telling me you posted something and never got one. Sorry about not checking this. I will look this over asap and post the related question we talked about. Thank you!

Hi Fernando,

Ran the code and it correctly gave me the option of which spreadsheet to use, so that is good.
However, it now is just hanging, the code apparently whirring away for over 5 minutes now and had to do a control-alt-delete to shut it down.

Could you tell me what is required for this code to run successfully? I might have something, or not have something in columns it is checking causing it to hang.

Hold on! Wow! That took awhile but I got results.  Great!

But still, could you tell me on what sheets the code is looking and what is required for this code to run? I want to make it got to the total correctly.

Now that was fast! About 2 seconds and it came up with a much different number than the other one.
Just so I can make sure that it is getting the right figures, could you tell me on what sheets the code is looking and what is required for this code to run? I want to make it got to the total correctly.

Thank you, though. It seems to work great!

well... it checks all the sheets according to the logic requested above.
Only sheets that do NOT start with MC. And it loops through all the sheets within the selected file, except the Final Report one.
Also, it will only add the numbers of sheets that has the Load Fee as text in the row one for any of the columns... if there's no column with this heading, it will not use that sheet.

Please run many times with the same file, in order to see how the results differ (or not)...

Thanks,

ran three times - all with the same results.

but have a question:
How does it know to grab the different month's Loading fee on each page?
That's what I'm trying to determine to make sure I don't have something on
the sheet or in the columns that is altering the totals.
What has to be in the Date column and the Load Fee column for this to be accurate
and how do they have to relate to one another. You have an example sheet of what the code has to go through. What would make the code work or not work?

see, the column K has necessarily to have a date...
the column L has to have numbers... (considering that sometimes it has text, which is not valid)

The code is using a array sum to add up all the values from column L that have the same month in column K... now, the next "project/question" will be how to make sure we populate column K with as much dates as possible, following that rules you mentioned above...

my suggestion is, in order to understand better and in details what the code is doing, you should do two things:

1) read this whole thread again, since the beginning (it will make it more clear all the logic used)
2) (most important for your tests) : Take one of your workbooks, save as different file break it make sure it has only 5 sheets ( or more, or less, just, make it easier to check ;) ) then run the code and verify the values... you will see that it's doing what I mentioned above, and it is ignoring values on column L that has no date right next to it on column K ...
3) repeat number 2 for as many workbooks as you want, then you'll become more confident that the results are accurate...

Makes sense ?

Let me know if you have any more doubts... I'll be happy to clarify.

I'll go ahead and do as you suggest, Fernando.
Are you up to the second part of this project which is automatically formatting and placing the date, calculating the amount to load and loading fee?

I'm closing this one out and hopefully you'll pick the related question up on the other side.

Thanks for your great work on this one.

I'll try the other one as well :)

who doesn't like a chalenge ? lol

no problem... let's see what the next steps will be !

Great work! Great guy! Pleasure to work with. He delivered a complex requirement in quick time.