totalling WU sheet and inserting values in totals spreadsheet.
gowflow,
Now, as the final part of summing of Mastercard for WU-Staging-Wire sheet we need to:
1. sum Col AA from the WU-Staging-FBME sheet where the date in Col A = previous month and insert that value into cell B21 of the created totals spreadsheet. And ...
2. sum Col AB from the WU-Staging-FBME sheet where the date in Col A = previous month and insert that value into cell C21 of the created totals spreadsheet.
Now, as the final part of summing of Mastercard for WU-Staging-Wire sheet we need to:
1. sum Col AA from the WU-Staging-FBME sheet where the date in Col A = previous month and insert that value into cell B21 of the created totals spreadsheet. And ...
2. sum Col AB from the WU-Staging-FBME sheet where the date in Col A = previous month and insert that value into cell C21 of the created totals spreadsheet.
ASKER
Oops, I'm sorry. The date for summing the previous month comes from Col K.
JaseSt,
Either your lost or I don't have your latest version !!!
Col J is the Date Col K is WU Balance !!!
Are we not talking about sheet WU-Staging-FBME ???
gowflow
Either your lost or I don't have your latest version !!!
Col J is the Date Col K is WU Balance !!!
Are we not talking about sheet WU-Staging-FBME ???
gowflow
ASKER
You don't have the latest. It is attached.
WU-Staging-FBME-example.xls
WU-Staging-FBME-example.xls
JaseSt,
Ok here it is
1) Make a new copy of your latest MC file and give it a new name.
2) open VBA and doubleclick on module1 and view 1 sub at a time.
3) Delete the Sub CreateMonthlyTotals.
4) Paste the below code after any end sub
5) Save and Exit the workbook
6) Open it and try it.
gowflow
Ok here it is
1) Make a new copy of your latest MC file and give it a new name.
2) open VBA and doubleclick on module1 and view 1 sub at a time.
3) Delete the Sub CreateMonthlyTotals.
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 'Wire-Staging-FBME' 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
Application.EnableEvents = False
Set WS = Sheets("Wire-Staging-FBME")
'---> 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 7/2/2012
'J = 3
'K = 1
'Date Col A
DateCol = 1
'---> Locate end month
Todate = Application.WorksheetFunction.EOMonth(SDate, 0)
'---> Filter Records 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("P:P").SpecialCells(xlCellTypeVisible)
NewWS.Range("B20") = Application.WorksheetFunction.Sum(RngE)
Set RngE = WS.Range("Q:Q").SpecialCells(xlCellTypeVisible)
NewWS.Range("C20") = Application.WorksheetFunction.Sum(RngE)
WS.ShowAllData
WS.AutoFilterMode = False
'---> Update WU-Staging-FBME
'Date Col K
DateCol = 11
Set WS = Sheets("WU-Staging-FBME")
'---> Filter Records 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("AA:AA").SpecialCells(xlCellTypeVisible)
NewWS.Range("B21") = Application.WorksheetFunction.Sum(RngE)
Set RngE = WS.Range("AB:AB").SpecialCells(xlCellTypeVisible)
NewWS.Range("C21") = Application.WorksheetFunction.Sum(RngE)
'---> Find the Coresponding figure in Sheet Final Report
ThisWorkbook.Activate
'---> 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
Application.EnableEvents = True
End Sub5) Save and Exit the workbook
6) Open it and try it.
gowflow
ASKER
Oh, something is definitely not working right.
Not only did it put $0.00 dollars for cells B20, B21, C20 and C21 it totally wiped out the Wire-Staging-FBME sheet and cleared out ALL the data, leaving only the column headers.
And as a side note, not sure why it always has me select where the Totals folder is.
Not only did it put $0.00 dollars for cells B20, B21, C20 and C21 it totally wiped out the Wire-Staging-FBME sheet and cleared out ALL the data, leaving only the column headers.
And as a side note, not sure why it always has me select where the Totals folder is.
ok first
Are you sure that Col AA and AB have already data there ? as understand we created these col just now.
second
Are you sure that for the month you selected you already have data ?
thrird
for the wiping out of the data it is not a problem here is the fix
replace the CreateMonthlyTotals by the below one.
fourth
About asking for the folder Total ??? Surprised as it does not ask me here !! to check pls look at in VBA in ThisWorkbook code in the Workbook_Deactivate event it should be like the code below. If not pls delete the code that is there and replace it by the below one.
gowflow
Are you sure that Col AA and AB have already data there ? as understand we created these col just now.
second
Are you sure that for the month you selected you already have data ?
thrird
for the wiping out of the data it is not a problem here is the fix
replace the CreateMonthlyTotals by the below one.
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 'Wire-Staging-FBME' 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
Application.EnableEvents = False
Set WS = Sheets("Wire-Staging-FBME")
'---> 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 7/2/2012
'J = 3
'K = 1
'Date Col A
DateCol = 1
'---> Locate end month
Todate = Application.WorksheetFunction.EOMonth(SDate, 0)
'---> Filter Records 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("P:P").SpecialCells(xlCellTypeVisible)
NewWS.Range("B20") = Application.WorksheetFunction.Sum(RngE)
Set RngE = WS.Range("Q:Q").SpecialCells(xlCellTypeVisible)
NewWS.Range("C20") = Application.WorksheetFunction.Sum(RngE)
'---> Update WU-Staging-FBME
'Date Col K
DateCol = 11
Set WS = Sheets("WU-Staging-FBME")
'---> Filter Records 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("AA:AA").SpecialCells(xlCellTypeVisible)
NewWS.Range("B21") = Application.WorksheetFunction.Sum(RngE)
Set RngE = WS.Range("AB:AB").SpecialCells(xlCellTypeVisible)
NewWS.Range("C21") = Application.WorksheetFunction.Sum(RngE)
'---> Find the Coresponding figure in Sheet Final Report
ThisWorkbook.Activate
WS.ShowAllData
WS.AutoFilterMode = False
'---> 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
Application.EnableEvents = True
End Subfourth
About asking for the folder Total ??? Surprised as it does not ask me here !! to check pls look at in VBA in ThisWorkbook code in the Workbook_Deactivate event it should be like the code below. If not pls delete the code that is there and replace it by the below one.
Private Sub Workbook_Deactivate() 'gstGenerateWUName
If gstFolderToMonitor <> "" Then SaveSetting APP_CATEGORY, APPNAME, "FolderToMonitor", gstFolderToMonitor
If gstFolderToTransfer <> "" Then SaveSetting APP_CATEGORY, APPNAME, "FolderToTransfer", gstFolderToTransfer
If gstFolderWesternUnion <> "" Then SaveSetting APP_CATEGORY, APPNAME, "FolderWesternUnion", gstFolderWesternUnion
If gstFolderVisaFile <> "" Then SaveSetting APP_CATEGORY, APPNAME, "FolderVisaFile", gstFolderVisaFile
If gstFolderMonthlyTotalsFile <> "" Then SaveSetting APP_CATEGORY, APPNAME, "FolderMonthlyTotalsFile", gstFolderMonthlyTotalsFile
If gstGenerateWUName <> "" Then SaveSetting APP_CATEGORY, APPNAME, "GenerateWUName", gstGenerateWUName
If gstGenerateWUEmail <> "" Then SaveSetting APP_CATEGORY, APPNAME, "GenerateWUEmail", gstGenerateWUEmail
If gstGenerateVisaName <> "" Then SaveSetting APP_CATEGORY, APPNAME, "GenerateVisaName", gstGenerateVisaName
If gstGenerateVisaEmail <> "" Then SaveSetting APP_CATEGORY, APPNAME, "GenerateVisaEmail", gstGenerateVisaEmail
End Subgowflow
ASKER
Ran new code. This time it didn't wipe the page, left it as it was, however it still is entering zeroes in those cells.
Do I have values in cells AA and AB and do I have the dates? Yes I do. Please see attached example sheets I ran the code off of.WU-Staging-FBME-example.xls
Do I have values in cells AA and AB and do I have the dates? Yes I do. Please see attached example sheets I ran the code off of.WU-Staging-FBME-example.xls
ASKER
Where do I find the Workbook_Deactivate event? I'm not seeing it.
Where do I find the Workbook_Deactivate event? I'm not seeing it.
>> goto vba doubleclick on Thisworkbook in the left pane and in the list of code you will find Workbook_Deactivate
I will be looking lateron at your file posted.
gowlfow
>> goto vba doubleclick on Thisworkbook in the left pane and in the list of code you will find Workbook_Deactivate
I will be looking lateron at your file posted.
gowlfow
Let me see here
What code is updating Col K ?? in WU-Staging-FBME I see a date there but in my files this col was not a date so surely not me who developped this. It maybe here the problem.
gowflow
What code is updating Col K ?? in WU-Staging-FBME I see a date there but in my files this col was not a date so surely not me who developped this. It maybe here the problem.
gowflow
ASKER
Nothing updates Col K. I manually insert it.
JaseSt,
I ran the code with the file you attached and it did update the values perfectly. Don't know what you did but seems your messed up with the versions. Anyhow I have attached the latest version of the Sub createMonthlyTotals for you.
1) Make a copy of your latest MC file.
2) Goto vba and choose to view 1 sub at a time. doubleclick on module1 and choose CreateMonthlyTotals and delete all the code for that sub.
3) Paste the below code after any end sub
OOOPSSS It seems they are upgrading the site in EE I cannot attach code so I attached it in the attach file !!!
[What a MESS --->>> directed to EE !!!]
4)pls SAVE the workbook at this stage and exit
5) Start again and give it a try.
gowflow
CreateMonthlyTotals.txt
I ran the code with the file you attached and it did update the values perfectly. Don't know what you did but seems your messed up with the versions. Anyhow I have attached the latest version of the Sub createMonthlyTotals for you.
1) Make a copy of your latest MC file.
2) Goto vba and choose to view 1 sub at a time. doubleclick on module1 and choose CreateMonthlyTotals and delete all the code for that sub.
3) Paste the below code after any end sub
OOOPSSS It seems they are upgrading the site in EE I cannot attach code so I attached it in the attach file !!!
[What a MESS --->>> directed to EE !!!]
4)pls SAVE the workbook at this stage and exit
5) Start again and give it a try.
gowflow
CreateMonthlyTotals.txt
ASKER
gowflow, why are you submitting a solution for the MC file? We are working on the Visa file. Or, did you mean this for the Visa file?
Yes Correct my mistake I meant the Visa file.
Sorry then I hope you did not insert this code in the mastercard file !!! My post should then read:
1) Make a copy of your latest Visa file.
2) Goto vba and choose to view 1 sub at a time. doubleclick on module1 and choose CreateMonthlyTotals and delete all the code for that sub.
.. etc
Pls let me know.
gowflow
Sorry then I hope you did not insert this code in the mastercard file !!! My post should then read:
1) Make a copy of your latest Visa file.
2) Goto vba and choose to view 1 sub at a time. doubleclick on module1 and choose CreateMonthlyTotals and delete all the code for that sub.
.. etc
Pls let me know.
gowflow
ASKER
Installed code in Visa file and not sure what is happening but still producing zeroes for the Visa file in the Totals spreadsheet for the Western Union cells (B21 and C21) The Wired cells (B20 and C20) are populating.
For WU-Staging-FBME I do have values in Col AA and AB and plenty of entries where the Col K date is January 2012, in the format of 01/24/12, for example.
For WU-Staging-FBME I do have values in Col AA and AB and plenty of entries where the Col K date is January 2012, in the format of 01/24/12, for example.
I do not understand what is happeneing, I took your file the WU-Staging-FBME that you posted and renamed the one that I had and ran the macro and it gave me figures in B21 and C21 like the file attached but B20 and C20 was zero I suspect normal as the sheet Wire-Staging-FBME that I have has old data !!!
I frankly do not know what is causing this.
gowflow
January-2012.xls
I frankly do not know what is causing this.
gowflow
January-2012.xls
ASKER
Can you post the WU-Staging-FBME sheet that you used?
The one you attached in ID post 37603814
gowflow
gowflow
ASKER
I know, but I wanted to see exactly what you input and how it compared or differed from mine.
I did nothing to the sheet I just moved it to the Visa workbook and renamed the old one WU-Staging-FBME old and ran the Create Monthly totals button and it produced the file I attached earlier !!!
Do you want me to post the entire Visa workbook here ???
gowflow
Do you want me to post the entire Visa workbook here ???
gowflow
ASKER
But you added dates to Col K I presume and also input values into Cols Y and Z? I would just like to see the sheet to see if there is anything different, but in lieu of that, I'll send you mine again, with those Cols filled out. Please run it with this sheet. WU-Staging-FBME-example3.xls
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
That worked! Now,I'm going to double check the totals summed, but I think that did it!
ok fine
gowflow
gowflow
ASKER
Thank you, gowflow. Another great solution you provided.
There are a couple of other pieces to this puzzle:
1. To take those results now in the Totals page and insert them into the correct cells of the Monthly-Totals-By-Year-Fin
2. How do I create the graph for 2012 in the Graphs sheet of Monthly-Totals-By-Year-Fin
I will ask point number 1, above, as a separate question if you think it is something you want to tackle. Point number 2 I just need a reminder as I think you told me.
There are a couple of other pieces to this puzzle:
1. To take those results now in the Totals page and insert them into the correct cells of the Monthly-Totals-By-Year-Fin
2. How do I create the graph for 2012 in the Graphs sheet of Monthly-Totals-By-Year-Fin
I will ask point number 1, above, as a separate question if you think it is something you want to tackle. Point number 2 I just need a reminder as I think you told me.
ok I am ready you can ask the questions that you like but pls put a link in here as with this version not really know if there is even a possibility to ask related questions.
gowlfow
gowlfow
ASKER
1. sum Col AA from the WU-Staging-FBME sheet where the date in Col A = previous month
>>> In WU-Staging-FBME Col A is Sequence Number Not a Date. The Date you have are
Col C: Date WU received
Col J: Date
Which one should we use to filter the sheet ?
gowflow