Mastercard totals button - part 3

1) regardless of date ?
2) What is the name of the file created ?
3) shall we right in Blankmonthlytotals and save it ?? if yes then it is no more blankmonthlytotals !!!!

Sees you are missing issues here
gowflow

get back to you in an hour or so

As mentioned before,

1: the date is the month before as I will run this for the last previous month. So for February I will run the totals for January and January 2012 only dates

2: The name of the file created is 'theMonththeYearTotals'. So running the code in February would create the file: 'January2012Totals.xls' . So yes,

3: You open blankmonthlytotals and save it as indicated in point #2 above.

yes ok but this was not explained in the question above it was stated in the previous question !!!
Questions need to be clear concise and contains all necessary info. Can oyu please edit the question again and put the missing info there ?
gowflow

Not sure how to edit this question.
The original question for the Mastercard workbook is:

1. Open spreadsheet, BlankMonthlyTotals.xls (attached)

From the Mastercard workbook:
2. Sum up the values from Col E of sheet 'MC Consolidated' where the date is last month's date and insert that total to cell B17 of BlankMonthlyTotals.xls
3. Copy the value from sheet 'Final Report' where the month = last month and paste that value to cell C17 of BlankMonthlyTotals.xls
4. Save BlankMonthlyTotals.xls as LastMonth-Year.xls

You do not have an attached file.

1. Prepare your data

Add the formula to change the date to month and year (see attached MC Consolidated)

2. Select the cell

Select Cell C17 of BlankMonthlyTotals.xls

3. Link 2 tables together

see attached BlanketMonthlyTotals.xlsx
No need to copy and paste, unless you do not like linking files together.  If MC Consolidated is updated, Blanket Monthly Totals will update.  Try it out.

FYI. to link to files together, you do not need to type out the formulas.  Just open both files.  When you type portions of your formula, you can click the other file to retrieve those fields.
MC-Consolidated.xlsx
BlankMonthlyTotals.xlsx

JaseSt,

Sorry for late reply on this due to maily new v.10 layout which leaving us Experts totally blindfolded with the iconic status removed and the layout beeing totally diffrent and less eye catching. Will attend asap if no other 'acceptable' solution beats us !

Rgds/gowflow

Yeah, and not only that, but I'm not getting email notifications when someone posts to this question.

please complain, I have strongly complained and advised them that without the notifications we cannot attend effectively actually since ystday I stoped answering quesitons it is a disaster !!
gowlfow

I let them know and then I saw an email notification you had responded. Strange. I'll have to monitor this post at the website level for awhile, just to make sure.

ok sorry for delay here it is:

1) Make a new copy of your latest MC file and give it a new name
2) goto vba and doubleclick Module1
3) Choose to view 1 sub at a time by clicking on the bottom left icon.
4) Paste the below code after any End Sub

Sub CreateMonthlyTotals()
Dim WS As Worksheet
Dim WSS As Worksheet
Dim NewWS As Worksheet
Dim MaxRow As Long, I As Long, J As Long, K As Long, DateCol As Long
Dim NewWb 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
        
        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
        Set WS = Sheets("Final Report")
        Set cCell = WS.UsedRange.Find(Format(SDate, "Mmm/yy"), 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
        
        'For I = 2 To MaxRow
        '    If WS.Range(I & ":" & I).EntireRow.Hidden = False Then
        '
        '        If J = 1 Then
        '            '---> Create the Header for the First Time
        '            'Customer Identifier Currency    Amount  Merchant Reference
        '            'disabled by gowflow on 14/1/2012 as we are now openeing the blank
        '            'worksheet that is sent from the bank that has already a header row
        '            NewWS.Cells(J, "A") = "Customer Identifier"
        '            NewWS.Cells(J, "B") = "Currency"
        '            NewWS.Cells(J, "C") = "Amount"
        '            NewWS.Cells(J, "D") = "Merchant Reference"
        '            NewWS.Range(J & ":" & J).Font.Bold = True
        '        Else
        '            '---> Copy Col B from Visa to Col A of New File
        '            NewWS.Cells(J, "A") = WS.Cells(I, "B")
        '
        '            '---> Copy Col E from Visa to Col C of New File
        '            NewWS.Cells(J, "C") = Format(WS.Cells(I, "E"), "€#,###.00")
        '
        '            '---> Put EUR to Col B of New File
        '            NewWS.Cells(J, "B") = "EUR"
        '
        '            '---> Insert 'smcurrentYearMonthDate#' Visa to Col D of New File
        '            NewWS.Cells(J, "D") = "sm" & Format(Year(SDate), "0000") & Format(Month(SDate), "00") & Format(Day(SDate), "00") & J - 1
        '        End If
        '        J = J + 1
        '    End If
        'Next I
        
        '---> Save Workbook
        NewWb.Save
                 
        '---> 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
End Sub

Select allOpen in new window

5) Save the workbook
6) check the below picture


7) Select sheet main make sure Design is selected in Developper menu and click on any button and copy it and place it like in the picture and color it blue and name it Create monthly totals. it should have following code:
============
Private Sub CommandButton7_Click()
CreateMonthlyTotals
End Sub
============

It may be commandbutton7 or not but put the line CreateMonthlyTotals there.

8) Save and Exit the workbook.
9) Open it and give it a trymake sure that the folder that is selected is the one that have the BlankmonthlyTotals.xls file and let me know.

NOTE I do not know how all this post will comeup as first time posting code and pic and instruction in this new format !!

gowflow

Thank you gowflow. Inserted and ran the code.

It correctly inserted the right total for the MC Total, Load in USD, in Col B17.
However, it inserted $0.00 for the Fees in cell C17 even though running the Final Report sheet gave the following:  "Jan/12      $ 3,817.69"

Yes I suspect it is not finding the correct cell can you pls attach the final report to an empty workbook and attach it here so I can see how it is ?
tks/gowflow

attached
FinalReportSheet.xlsm

Perfect, gowflow!

Now, if you are willing, we do the same thing with the Visa Workbook but that will be broken into two parts: one for the Wire-Staging-FBME sheet and one for the WU-Staging-FBME sheet, starting with Wire-Staging-FBME as it will be the easiest I think.

Don't see where I can ask a related question, so here is the next step:

https://www.experts-exchange.com/questions/27572622/Visa-Totals-to-totals-spreadsheet-related-question-from-Mastercard-totals-button-part-3.html

Totally confuesed with this new layout !!!! your right don't see either the related question maybe in this window there is link maybe this will do ???

Anyway tks for the grade and will attend on my way back hv to go out now.
Rgds/gowflow