Sum up commissions from Mastercard and Visa Spreadsheets part 1

part 1 because this is the Mastercard part of the solution requested.

In the Mastercard workbook I have sheet names preceded by 'HMF'. I need a function that will go thru all those sheets and sum the values in Col M (Commission Balance) for a determined date range that I select.

That sum then needs to be inserted into the attached spreadsheetHMF-Commision-Report.xls
JaseStAsked:
Who is Participating?
 
gowflowConnect With a Mentor Commented:
ok here it is:

1) Make a new copy of your latest MC file and give it a new name.
2) open it and goto VBA and doubleclick on module1 and after any End Sub paste the below code

Sub SumUpHMFComissions()
Dim NewWB As Workbook
Dim ThisWB As Workbook
Dim WS As Worksheet
Dim NewWS As Worksheet
Dim MaxRow As Long, I As Long, DateCol As Long
Dim HMFCommission As Double
Dim Todate As Date, SDate As Date
Dim RngE As Range
Dim cCell As Range

If gstFolderMonthlyTotalsFile = "" Then
    MsgBox ("You need to select a destination folder to store the Monthly Totals/HMF-Commission-Report File created. Please go to Sheet 'Main' and select a folder before proceeding further.")
    Exit Sub
Else
    If MsgBox("This process will Update file 'HMF-Commission-Report.xls' with the total comissions from Col M in all of HMF worksheets for the date range selected." & Chr(10) & Chr(10) _
        & "Are you ready to start this process ?", vbQuestion + vbYesNo, "HMF Commissions") = vbYes Then
        
        Application.ScreenUpdating = False
        Set ThisWB = ActiveWorkbook
        
        '---> Input Start Date
        Do
            SDate = InputBox("Please input Date Start", "HMF Commission Start Date", DateValue(Now))
        Loop Until IsDate(SDate)
        
        '---> Input To Date
        Do
            Todate = InputBox("Please input Date End", "HMF Commission End Date", DateValue(Now))
        Loop Until IsDate(Todate)
        
        
        '---> new procedure for opening the default blankMonthlyTotals set by gowflow on 3/2/2012
        On Error Resume Next
        Application.AutomationSecurity = msoAutomationSecurityForceDisable
        Application.EnableEvents = False
        Set NewWB = Workbooks.Open(Filename:=gstFolderMonthlyTotalsFile & "HMF-Commision-Report.xls")

        Application.EnableEvents = True
        If err <> 0 Then
            MsgBox ("HMF-Commision-Report.xls file was not found please create it or adjust file folder location then re-start this procedure.")
            Exit Sub
        End If
        On Error GoTo 0
        
        Set NewWS = NewWB.ActiveSheet
        MaxRow = NewWS.UsedRange.Rows.count + 1
        MsgBox ("File HMF-Commision-Report.xls was found and will Update necessary info.")

        
        '---> Go thru all shteets in the current workbook except a list of them and
        '---> Summ Col L for all records where Date in Col K = the month before
        '---> Put that Value in Cell C17 of BlankMonthlyTotals file
        HMFCommission = 0
        '---> Col K
        DateCol = 11
        
        '---> Cumulate Sum of all Col M where Date range is repsected in Col K
        For Each WS In ThisWB.Worksheets
            
            If UCase(Left(WS.Name, 3)) = "HMF" Then
                '---> Filter Records in Col K where Date between SDate and ToDate
                WS.UsedRange.AutoFilter Field:=DateCol, Criteria1:=">=" & SDate, Operator:=xlAnd, Criteria2:="<=" & Todate
                
                '---> Affect to RngE the visible data
                '---> Summ All Values in Col L that meet last month date and affect to C17
                Set RngE = WS.Range("M:M").SpecialCells(xlCellTypeVisible)
                HMFCommission = HMFCommission + Application.WorksheetFunction.Sum(RngE)
                WS.ShowAllData
                WS.AutoFilterMode = False
            End If
        
        Next WS
        
        DateRange = Format(SDate & " to " & Todate)
        
        '---> find if Same occurence already there update the same row
        Set cCell = NewWS.UsedRange.Find(what:=DateRange, LookIn:=xlValues, lookat:=xlWhole, MatchCase:=False)
        
        
        If Not cCell Is Nothing Then
            '---> Update Row Found with the Values
            NewWS.Cells(cCell.Row, "D") = HMFCommission
            NewWS.Cells(cCell.Row, "D").NumberFormat = "\$#,##0.00_);[Red]($#,##0.00)"
        Else
            '---> Update New Record with the Values
            NewWS.Cells(MaxRow, "B") = DateRange
            NewWS.Cells(MaxRow, "D") = HMFCommission
            NewWS.Cells(MaxRow, "D").NumberFormat = "\$#,##0.00_);[Red]($#,##0.00)"
        End If
    
        '---> Close Commission Workbook.
        NewWB.Save
        NewWB.Close
        Application.ScreenUpdating = True
        
        '---> Give Feedback
        MsgBox ("HMF-Commission-Report.xls have been updated with the commissions related to date range from " & DateRange & " successfully.")
    
    End If

End If

End Sub

Open in new window


3) SAVE the workbook.
4) Open sheet Main and in the bottom under the last commandbutton put a new button like the below picture


new button
You can choose the developper menu and press on design and click on the blue button and copy it and change its caption like in the picture.

double click on the new created button and put this
SumUpHMFComissions

5) SAVE and exit the workbook
6) Open it and give it a try.

gowflow
0
 
NBVCCommented:
It makes it more difficult when working across workbooks to do this sort of thing.

I would suggest you do the main work in the Mastercard workbook, then directly reference result cells to the summary.

Here is the method to use in the Mastercard workbook:

http://www.xl-central.com/sum-single-criteria-multiple-sheets.html

You can substitute SUMIFS for SUMIF if using XL2007 or later and have multiple criteria (i.e start and end date)
0
 
gowflowCommented:
Will attend asap
gowflow
0
Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

 
gowflowCommented:
Is this a new function or already done ?
gowflow
0
 
JaseStAuthor Commented:
this is a new function, but similar, or course, to work you've already done with summing totals from tabs
0
 
gowflowCommented:
what is the date ? the previous month like usual ?
gowflow
0
 
JaseStAuthor Commented:
No. As mentioned above, I need to manually select a date range:

"I need a function that will go thru all those sheets and sum the values in Col M (Commission Balance) for a determined date range that I select."
0
 
JaseStAuthor Commented:
now it's me waiting for you instead of you waiting for me :)

got anything with this yet, gowflow? Customer has need for me to come with the figures and I loath having to go through all these pages and manually adding them up.

Thanks for your help.
0
 
NBVCCommented:
Have you considered my suggestion?
0
 
JaseStAuthor Commented:
thanks NB_VC but the function needs to sum values from a specific column where the date = a range I select from a different column.
0
 
JaseStAuthor Commented:
Gowflow you there? Vacation? Computer problems?
0
 
gowflowCommented:
No here sorry for overlooking this will attend shortly.
gowflow
0
 
gowflowCommented:
ok as this is affecting both Visa and MC can we assume that this file
HMF-Commision-Report.xls
will be located in the same directory as the CreateMonthlyTotals folder ? if yes then this will avoid putting new buttons to select location for that file. Also can we agree that the name will be the name of the file oyu posted in here so that it is hard coded in both workbooks ?

if you agree with above then this should make the task lighter and speedier to execute.
Also seems that you want to choose a date range not specifically a month so it could be from 2/3/2013 to 26/3/2013 etc ... right ? and each request will update a row in the file ?

Last but not least do you have a problem with the following
HMF Commissions      Date Range                                      Visa      Mastercard
                                    2/3/2013 to 26/3/2013                        1200
                                    2/3/2013 to 26/3/2013                                                     1500

or you want them like this
                                    2/3/2013 to 26/3/2013                         1200                     1500

gowflow
0
 
JaseStAuthor Commented:
yes, to all your questions, however, would like the previous totals displayed, like::

12/2/2012 to 01/12/2013:
1/12/2013 to 2/4/2013:
2/4/2013 to 3/15/2013:
and yes, visa and mastercard can be on one line
0
 
JaseStAuthor Commented:
Thanks, gowflow. I ran it but the total is quite different that when I added it manually. I come up with $171.25 for the HMF Account page and then $581.99 for all the pages with HMF as the first letters of the name of the sheet.
0
 
JaseStAuthor Commented:
I will go through it again and make sure I didn't miss something. I should have stated that your macro came up with $830.46 and I manually added $753.24
0
 
gowflowCommented:
make sure of the date range it takes into consideration both limits
from 1/1/2013 to 1/31/2013 includes both 1 and 31.

Also something else comes to mind as I often saw in the past you hv tendancy to put comments in the Amount field so here again if you have in Col M something  like
83.45 but this was a return ...
like amount and some text coments or whatever then for sure this is concidered as text and will not be included in the total ! Just though good to mention this.
gowflow
0
 
JaseStAuthor Commented:
Your sum is correct.  I just missed a value. Thank you very much for your help, gowflow. I'm going to skip doing the Visa part for now as it is very easy to sum up manually. I will ask for your help if that changes. Thanks again, gowflow. It is much appreciated.
0
 
gowflowCommented:
Tks. any other help needed pls do not spare me.
Rgds/gowflow
0
 
JaseStAuthor Commented:
hi gowflow! Ready for another one?
0
 
gowflowCommented:
yep
gowflow
0
 
JaseStAuthor Commented:
gowflow - I have one project too complex to do right now or explain - and am not sure how it can be done, but in the meantime I have another.

If you remember the functions for both the Visa and Mastercard workbooks that calculates the monthly totals for the previous month, I have an additional feat I would like that macro to do and please tell me if it can be done and if you can/want to do it.

It is this. I hope I can describe it understandably.

- have the function search two specific folders in my Outlook where the emails have the date = to last month. Then look for specific email addresses within that batch, total the number of those specific email address and put that total in specific cells of the Monthly Totals spreadsheet.

If you think this can be done, let me know and I will post a question and the link to it here.
0
 
gowflowCommented:
you said:


I have one project too complex to do right now or explain

Is this what you tried to exaplain later ... the lookup in outlook for 2 folders etc ... or it is something else ?

For the lookup in outlook for 2 folders yes I can assist.

If you previous answer is NO and it is a total diffrent other project then I am willing to go private and assist you basis a reasonable fee. If your intrested let me know.

gowflow
0
 
JaseStAuthor Commented:
It is the lookup in two folders. I will explain in a question I will post in a day or two.

Thanks for your offer to help, yet once again.
0
 
gowflowCommented:
When I looked at it then you were already well served. Let me know if u need other help.
gowflow
0
 
JaseStAuthor Commented:
yes, I do, gowflow - thank you - but it is a problem with a previous solution and I will post a link to it here
0
 
JaseStAuthor Commented:
http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/Q_28078554.html

Here it is gowflow. Remember when I mentioned that I wanted the MCR entries to NOT create a space? Well, it has caused another problem that I'm just getting around to telling you. Thanks for your efforts. Be nice to get this one fixed.
0
 
gowflowCommented:
Is this MC or Visa ?
gowflow
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.