Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 381
  • Last Modified:

Updating previous question - Create Monthly Totals for Mastercard workbook

Gowflow.

You worked on this before but I need an update to the function. It is related to question http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/Q_27563074.html, which has a few related questions that make up the total solution.

You have the Mastercard excel 2007 workbook, containing over 100 sheets.

On sheet Main, when I click the button "Create Monthly Totals", here's what it needs to do:

1. From sheet MC Consolidated, sum the values in Col E where the date in col J = the month before, and insert that summed value into B17 of BlankMonthlyTotals.xls

2. Go through the rest of the sheets to sum the total and insert that value into of BlankMonthlyTotals.xls col C17 as follows:

For all sheets (except Main, Final Report, MC Heritage Balance, MC Goga Tora, MC Reseller Offshore, MC OffShoreCommission, MC Reseller-GMT, MC HMF Cardholders and MC Consolidated)  where the date in Col K = the month before, sum Col L and insert that value into of BlankMonthlyTotals.xls col C17

3. Then as it already does, save BlankMonthlyTotals.xls as LastMonth-Year.xls

Let me know if there's anything else you need or if I need to clarify.
0
JaseSt
Asked:
JaseSt
  • 11
  • 11
1 Solution
 
gowflowCommented:
Sorry deeply sorry was on vacation and got this totally out of track as had a pc crash that toook me litterally 2 weeks to get back on my feet. !!!!!

Will look at this asap and get back to you. Are u still intrested in the solution before I jump into it again ?

gowflow
0
 
JaseStAuthor Commented:
yes, waiting for your one of your great solutions! thank you.
0
 
gowflowCommented:
Can you pls advise what section is diffrent of what was done before ? I feel Section 2) never existed is that correct?

You need to tell me what was there before and what is diffrent now.
Rgds/gowflow
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
JaseStAuthor Commented:
Well, I thought point 2 was supposed to be there as I need the totals from the pages I reference as well. If you mean the pages, yes those pages were there before.
0
 
gowflowCommented:
Well then let me rephrase. This procedure CreateMonthlyTotals existed right ? So what is the change now what you need to be done that was not doing before ? I need to know where to look in this procedure to make my life and obviously yours easier and quicker.

So you need to tell me when you run the procedure now as it is what are the results that are no satissfactorly to you and what should you get instead so I understand more. As this looping thru all sheets surely does not exist. Instead it pulls from Sheet Final Report 1 value and plug it in cell C17 !!!! Is that the way it need to be done ? Maybe you need Final Report preparing to be modified then ????

gowflow
0
 
JaseStAuthor Commented:
Yes, it is the Create Monthly Totals on the Mastercard workbook.

It seems to go through all the tabs, summing the loading fee from Col L where the date in Col K is last month for every sheet that it is supposed to EXCEPT for some reason it is not including these sheets:

MCR Eforex Customers, MCR Eforex Staff, HMF Account, MCR AsiaCom

I need the loading fees (Col L) from all pages (except Main, Final Report, MC Heritage Balance, MC Goga Tora, MC Reseller Offshore, MC OffShoreCommission, MC Reseller-GMT, MC HMF Cardholders and MC Consolidated) where the date from Col L = last month AND THEN put that total in the correct cell: C17 of the spreadsheet created. (For last month that spreadsheet created was August-2012.xls)

Is that clearer?
0
 
gowflowCommented:
Can you please post the Sub CreateMonthlytotals that you have as it seems it was maybe altered by someone else and will take it fm there.
gowflow
0
 
JaseStAuthor Commented:
The below is from Module 1:

Sub CreateMonthlyTotals()
Dim WS As Worksheet
Dim WSS As Worksheet
Dim NewWS As Worksheet
Dim GraphWS As Worksheet
Dim MaxRow As Long, I As Long, J As Long, K As Long, DateCol As Long
Dim NewWb As Workbook
Dim GraphWb As Workbook
Dim NewWorkB As String
Dim EOMonth As String, 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 File created. Please go to Sheet 'Main' and select a folder before proceeding further.")
    Exit Sub
Else
    If MsgBox("This process will create a new workbook with Last Month's date and load in it all records in sheets 'MC Consolidated' that bear Last Moanth's date." & Chr(10) & Chr(10) _
        & "Are you ready to start this process ?", vbQuestion + vbYesNo, "Create Monthly Totals") = vbYes Then
       
        Application.ScreenUpdating = False
        Set WS = Sheets("MC Consolidated")
       
        '---> Setup Last Month Date
        If Month(Now) = 1 Then
            SDate = DateSerial(Year(Now) - 1, Month(Now) - 1, 1)
        Else
            SDate = DateSerial(Year(Now), Month(Now) - 1, 1)
        End If
       
        '---> Check if file already Exist then Open it else create the file from BlankMonthlyTotals and
        '     Name it to Previous Month's date
        On Error Resume Next
        Application.AutomationSecurity = msoAutomationSecurityForceDisable
        Application.EnableEvents = False
        Set NewWb = Workbooks.Open(Filename:=gstFolderMonthlyTotalsFile & Format(SDate, "Mmmm") & "-" & Format(SDate, "yyyy") & ".xls")
        Application.EnableEvents = True
       
        If err <> 0 Then
            MsgBox ("File " & Format(SDate, "Mmmm") & "-" & Format(SDate, "yyyy") & ".xls was not found will create it from 'BlankMonthlyTotals.xls'")
            On Error GoTo 0

       
            '---> 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 & "BlankMonthlyTotals.xls")
   
            Application.EnableEvents = True
            If err <> 0 Then
                MsgBox ("Default blank file 'BlankMonthlyTotals.xls' was not found please create it or adjus file folder location then re-start this procedure.")
                Exit Sub
            End If
            On Error GoTo 0
       
        Else
            MsgBox ("File " & Format(SDate, "Mmmm") & "-" & Format(SDate, "yyyy") & ".xls was found and will Update necessary info.")

        End If
 
        '---> Set Activesheet
        Set NewWS = NewWb.ActiveSheet
       
        Application.EnableEvents = False
        Application.DisplayAlerts = False
        NewWb.SaveAs Filename:=gstFolderMonthlyTotalsFile & Format(SDate, "Mmmm") & "-" & Format(SDate, "yyyy") & ".xls", FileFormat:=xlExcel8
        Application.EnableEvents = True
        Application.DisplayAlerts = True
        NewWorkB = NewWb.Name
       

        '---> updated by gowflow on 3/2/2012
        'J = 3
        'K = 1
        'Date Col J
        DateCol = 10
       
        '---> Locate end month
        Todate = Application.WorksheetFunction.EOMonth(SDate, 0)
       
        '---> Filter Records in Col J where Date between SDate and ToDate
        WS.UsedRange.AutoFilter Field:=DateCol, Criteria1:=">=" & SDate, Operator:=xlAnd, Criteria2:="<" & Todate
       
        '---> Affect to RngE the visible data
        Set RngE = WS.Range("E:E").SpecialCells(xlCellTypeVisible)
        NewWS.Range("B17") = Application.WorksheetFunction.Sum(RngE)
        WS.ShowAllData
        WS.AutoFilterMode = False
       
        '---> Find the Coresponding figure in Sheet Final Report
        ThisWorkbook.Activate
        Set WS = Sheets("Final Report")
        Set cCell = WS.UsedRange.Find(what:=Format(SDate, "Mmm/yy"), after:=WS.Range("A1"), LookIn:=xlFormulas, lookat:=xlWhole, MatchCase:=False)
        If Not cCell Is Nothing Then
            NewWS.Range("C17") = cCell.Offset(, 1).Value
        Else
            NewWS.Range("C17") = 0
        End If
       
        '---> Update Values to File 'Monthly-Totals-By-Year.xls'
        '---> open the graph file 'Monthly-Totals-By-Year.xls' set by gowflow on 29/2/2012
        On Error Resume Next
        Application.AutomationSecurity = msoAutomationSecurityForceDisable
        Application.EnableEvents = False
        Set GraphWb = Workbooks.Open(Filename:=gstFolderMonthlyTotalsFile & "Monthly-Totals-By-Year.xls")

        Application.EnableEvents = True
        If err <> 0 Then
            '---> File was not found in the gstFolderMonthlyTotalsFile location or not named correctly. Exit
            MsgBox ("Graph file 'Monthly-Totals-By-Year.xls' was not found please create it or adjust file folder location then re-start this procedure.")
            Exit Sub
        End If
        On Error GoTo 0
       
        On Error Resume Next
        Set GraphWS = GraphWb.Worksheets(Format(Year(SDate)))
        If err <> 0 Then
            '---> Year is not opened in 'Monthly-Totals-By-Year.xls' ask user to open it and Exit
            MsgBox ("In the Graph file 'Monthly-Totals-By-Year.xls' the year " & Year(SDate) & " is not opened yet. Please proceed to create this sheet then start this procedure again.")
            Exit Sub
        End If
        On Error GoTo 0
       
        '---> Year was found so Update values in proper columns
        '---> Find the Proper month Column
        Set cCell = GraphWS.Range("2:2").Find(what:=Format(SDate, "Mmm"), LookIn:=xlValues, lookat:=xlWhole, MatchCase:=True)
        If Not cCell Is Nothing Then
           
            '---> Update the values in the cCell.column
            '---> MC Load USD
            GraphWS.Cells(3, cCell.Column) = NewWS.Range("B17")
            '---> MC Fees USD
            GraphWS.Cells(4, cCell.Column) = NewWS.Range("C17")
           
            '---> Save and Close Graph file
            GraphWb.Save
            GraphWb.Close
        Else
            '---> The Month was not found advise user and Exit
            MsgBox ("In the Graph file 'Monthly-Totals-By-Year.xls' the year " & Year(SDate) & ", the month " & Format(SDate, "Mmm") & " was not found. Please proceed to check the file and then start this procedure again.")
            Exit Sub
        End If
       
        '---> Save Workbook
        NewWb.Save
        NewWb.Activate
       
        '---> Check if there was data in the file if yes the update variables to send file if no kill the file saved
        MaxRow = NewWS.UsedRange.Rows.count
       
        If MaxRow > 2 Then
            gstGenerateVisaName = NewWb.FullName
            gstGenerateVisaEmail = ""
            x = MsgBox("Workbook: '" & NewWorkB & "' has been successfully update. Please check workbook to ensure all data is accurate. After all modifications done please ensure file is saved to proceed to Next Step.", vbInformation, "Monthly Totals File")
        Else
            Application.DisplayAlerts = False
            NewWb.Close savechanges:=False
            Kill NewWorkB
            Application.DisplayAlerts = True
            gstGenerateVisaName = ""
            gstGenerateVisaEmail = ""
            MsgBox ("No Records were found ! nothing to Export.")
        End If
    End If
End If

Application.ScreenUpdating = True
End Sub
0
 
gowflowCommented:
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.
3) Click on the bottom left icon to view 1 sub at a time and display the Sub CreateMonthlyTotals and delete it.
4) Paste the below sub after any End Sub

Sub CreateMonthlyTotals()
Dim WS As Worksheet
Dim WSS As Worksheet
Dim NewWS As Worksheet
Dim GraphWS As Worksheet
Dim MaxRow As Long, I As Long, J As Long, K As Long, DateCol As Long
Dim NewWb As Workbook
Dim GraphWb As Workbook
Dim NewWorkB As String
Dim EOMonth As String, Todate As Date, SDate As Date
Dim RngE As Range
Dim cCell As Range
Dim WSToExclude As String

If gstFolderMonthlyTotalsFile = "" Then
    MsgBox ("You need to select a destination folder to store the Monthly Totals File created. Please go to Sheet 'Main' and select a folder before proceeding further.")
    Exit Sub
Else
    If MsgBox("This process will create a new workbook with Last Month's date and load in it all records in sheets 'MC Consolidated' that bear Last Moanth's date." & Chr(10) & Chr(10) _
        & "Are you ready to start this process ?", vbQuestion + vbYesNo, "Create Monthly Totals") = vbYes Then
        
        Application.ScreenUpdating = False
        Set WS = Sheets("MC Consolidated")
        
        '---> Setup Last Month Date
        If Month(Now) = 1 Then
            SDate = DateSerial(Year(Now) - 1, Month(Now) - 1, 1)
        Else
            SDate = DateSerial(Year(Now), Month(Now) - 1, 1)
        End If
        
        '---> Check if file already Exist then Open it else create the file from BlankMonthlyTotals and
        '     Name it to Previous Month's date
        On Error Resume Next
        Application.AutomationSecurity = msoAutomationSecurityForceDisable
        Application.EnableEvents = False
        Set NewWb = Workbooks.Open(Filename:=gstFolderMonthlyTotalsFile & Format(SDate, "Mmmm") & "-" & Format(SDate, "yyyy") & ".xls")
        Application.EnableEvents = True
        
        If err <> 0 Then
            MsgBox ("File " & Format(SDate, "Mmmm") & "-" & Format(SDate, "yyyy") & ".xls was not found will create it from 'BlankMonthlyTotals.xls'")
            On Error GoTo 0

        
            '---> 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 & "BlankMonthlyTotals.xls")
    
            Application.EnableEvents = True
            If err <> 0 Then
                MsgBox ("Default blank file 'BlankMonthlyTotals.xls' was not found please create it or adjus file folder location then re-start this procedure.")
                Exit Sub
            End If
            On Error GoTo 0
        
        Else
            MsgBox ("File " & Format(SDate, "Mmmm") & "-" & Format(SDate, "yyyy") & ".xls was found and will Update necessary info.")

        End If
 
        '---> Set Activesheet
        Set NewWS = NewWb.ActiveSheet
        
        Application.EnableEvents = False
        Application.DisplayAlerts = False
        NewWb.SaveAs Filename:=gstFolderMonthlyTotalsFile & Format(SDate, "Mmmm") & "-" & Format(SDate, "yyyy") & ".xls", FileFormat:=xlExcel8
        Application.EnableEvents = True
        Application.DisplayAlerts = True
        NewWorkB = NewWb.Name
        

        '---> updated by gowflow on 3/2/2012
        'J = 3
        'K = 1
        'Date Col J
        DateCol = 10
        
        '---> Locate end month
        Todate = Application.WorksheetFunction.EOMonth(SDate, 0)
        
        '---> Filter Records in Col J 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 E that meet last month date and affect to B17
        Set RngE = WS.Range("E:E").SpecialCells(xlCellTypeVisible)
        NewWS.Range("B17") = Application.WorksheetFunction.Sum(RngE)
        WS.ShowAllData
        WS.AutoFilterMode = False
        

' Cancelled by gowflow on 21/9/2012 as per new request fm JaseSt to be replaced by next Group
'================================================================
'        '---> Find the Coresponding figure in Sheet Final Report
'        ThisWorkbook.Activate
'        Set WS = Sheets("Final Report")
'        Set cCell = WS.UsedRange.Find(what:=Format(SDate, "Mmm/yy"), after:=WS.Range("A1"), LookIn:=xlFormulas, lookat:=xlWhole, MatchCase:=False)
'        If Not cCell Is Nothing Then
'            NewWS.Range("C17") = cCell.Offset(, 1).Value
'        Else
'            NewWS.Range("C17") = 0
'        End If
'=================================================================


' Replacing Section by gowflow on 21/9/2012 as per new request fm JaseSt
'=================================================================
        
        '---> 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
        WSToExclude = "Main/Final Report/Final Report1/MC Heritage Balance/MC Goga Tora/MC Resseller Offshore/MC OffshoreCommission/MC Resseller-GMT/MC HMF Cardholders/MC Consolidated"
        WS.Activate
        
        For Each WS In ActiveWorkbook.Worksheets
            
            If InStr(1, WSToExclude, WS.Name) = 0 Then
                '---> Filter Records in Col K where Date between SDate and ToDate
                WS.UsedRange.AutoFilter Field:=DateCol + 1, 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("L:L").SpecialCells(xlCellTypeVisible)
                NewWS.Range("C17") = NewWS.Range("C17") + Application.WorksheetFunction.Sum(RngE)
                WS.ShowAllData
                WS.AutoFilterMode = False
            End If
        
        Next WS
'=================================================================
        
        '---> Update Values to File 'Monthly-Totals-By-Year.xls'
        '---> open the graph file 'Monthly-Totals-By-Year.xls' set by gowflow on 29/2/2012
        On Error Resume Next
        Application.AutomationSecurity = msoAutomationSecurityForceDisable
        Application.EnableEvents = False
        Set GraphWb = Workbooks.Open(Filename:=gstFolderMonthlyTotalsFile & "Monthly-Totals-By-Year.xls")

        Application.EnableEvents = True
        If err <> 0 Then
            '---> File was not found in the gstFolderMonthlyTotalsFile location or not named correctly. Exit
            MsgBox ("Graph file 'Monthly-Totals-By-Year.xls' was not found please create it or adjust file folder location then re-start this procedure.")
            Exit Sub
        End If
        On Error GoTo 0
        
        On Error Resume Next
        Set GraphWS = GraphWb.Worksheets(Format(Year(SDate)))
        If err <> 0 Then
            '---> Year is not opened in 'Monthly-Totals-By-Year.xls' ask user to open it and Exit
            MsgBox ("In the Graph file 'Monthly-Totals-By-Year.xls' the year " & Year(SDate) & " is not opened yet. Please proceed to create this sheet then start this procedure again.")
            Exit Sub
        End If
        On Error GoTo 0
        
        '---> Year was found so Update values in proper columns
        '---> Find the Proper month Column
        Set cCell = GraphWS.Range("2:2").Find(what:=Format(SDate, "Mmm"), LookIn:=xlValues, lookat:=xlWhole, MatchCase:=True)
        If Not cCell Is Nothing Then
            
            '---> Update the values in the cCell.column
            '---> MC Load USD
            GraphWS.Cells(3, cCell.Column) = NewWS.Range("B17")
            '---> MC Fees USD
            GraphWS.Cells(4, cCell.Column) = NewWS.Range("C17")
            
            '---> Save and Close Graph file
            GraphWb.Save
            GraphWb.Close
        Else
            '---> The Month was not found advise user and Exit
            MsgBox ("In the Graph file 'Monthly-Totals-By-Year.xls' the year " & Year(SDate) & ", the month " & Format(SDate, "Mmm") & " was not found. Please proceed to check the file and then start this procedure again.")
            Exit Sub
        End If
        
        '---> Save Workbook
        NewWb.Save
        NewWb.Activate
        
        '---> Check if there was data in the file if yes the update variables to send file if no kill the file saved
        MaxRow = NewWS.UsedRange.Rows.count
        
        If MaxRow > 2 Then
            gstGenerateVisaName = NewWb.FullName
            gstGenerateVisaEmail = ""
            x = MsgBox("Workbook: '" & NewWorkB & "' has been successfully update. Please check workbook to ensure all data is accurate. After all modifications done please ensure file is saved to proceed to Next Step.", vbInformation, "Monthly Totals File")
        Else
            Application.DisplayAlerts = False
            NewWb.Close savechanges:=False
            Kill NewWorkB
            Application.DisplayAlerts = True
            gstGenerateVisaName = ""
            gstGenerateVisaEmail = ""
            MsgBox ("No Records were found ! nothing to Export.")
        End If
    End If
End If

Application.ScreenUpdating = True
End Sub

Open in new window


5) Save and Exit the workbook.
6) Start it again and give it a try.

PS: While debugging the sub to make sure calculation is correct I noticed that in sheet MCR Efforex Staff and MCR Efforex Customers you have hardly anything in Col L (Load Fee) so this may explain the fact that it seems for you it is not taking these sheet into account. At least the version I have here has noting in this column except lately maybe you used to not record these figures here ???? Did not check further sheets but the logic is fine.

One thing for sure the sub as it was before NEVER calculated the loding fees for all sheets it is a new routine I added now if you say it was adding it then maybe it is an other routine that was doing that but for sure not Create MonthlyTotals (That is if no one else modified this routine)

Anyway check it and let me know

gowflow
0
 
JaseStAuthor Commented:
Thanks, gowflow. I will check this out soon.

I will have a modification to another routine you developed for me where we are calculating the loading fee. It needs to be different for some pages.  I'll let you know here when posted.

Thanks again.
0
 
gowflowCommented:
Appreciate if we get done with this one b4 we move on to an other issue as it has been a while and obviously all this code is no more fresh in my memeory so lets take it 1 step at a time this way we can be more efficient.
gowflow
0
 
gowflowCommented:
Did you have a chance to try the proposed solution ?
gowflow
0
 
gowflowCommented:
Any news ?
gowflow
0
 
JaseStAuthor Commented:
Hi gowflow. I wanted to wait until we were into the next month, which we are now. I just ran it for September. Seemed to work but need to double check the loading fee amounts totaled for the Mastercard workbook. I'll get back to you shortly. Thanks.
0
 
JaseStAuthor Commented:
Manually checked it out and here are the results:

Mastercard totals almost exact. I might have made a mistake in adding so I consider it good.

Visa wired totals: exact
Visa Western Union totals: load is USD amount is about 15K off. I added col AA manually and the amount your function calculates is 15K less. I would also assume the USD Fees column is off as well.

Could you tell me the formula you are using to calculate the total USD Loaded (from Col AA) in the WU-Staging-FBME sheet for September?
0
 
JaseStAuthor Commented:
Hi goflow. You there?

Could you tell me the formula you are using to calculate the total USD Loaded (from Col AA) in the WU-Staging-FBME sheet for September? I think the load amount for USD amount (col AA - WU-Staging-FBME) is about 15K off.

I added col AA manually and the amount your function calculates is 15K less.
0
 
gowflowCommented:
Sorry again I lost 2 pcs and was unqualified in EE reason why I could not attend back now on track. Let me look at this and will revert sorry for this.
gowflow
0
 
gowflowCommented:
OOOPs looking at this in more details you ask about WU-Staging-FBME but this sheet is in the Visa file and this question is about the Mastercard file so I am abit confused !!!!
gowflow
0
 
JaseStAuthor Commented:
I'm sorry. Yes, you're right. The Visa totals is a separate question. I'll accept this solution and post the Visa aspect to this which is where I'm finding a 15K difference.
0
 
JaseStAuthor Commented:
Thanks gowflow. As always, great work!
0
 
gowflowCommented:
ok pls post the link if you need assistance in here for the visa file.
Tks/Rgds
gowflow
0

Featured Post

Become an Android App Developer

Ready to kick start your career in 2018? Learn how to build an Android app in January’s Course of the Month and open the door to new opportunities.

  • 11
  • 11
Tackle projects and never again get stuck behind a technical roadblock.
Join Now