Oberon70
Board Regular
- Joined
- Jan 21, 2022
- Messages
- 160
- Office Version
- 365
- Platform
- Windows
Hi,
I want to thank everyone who has helped me in past. I have only been ready and learning about VBA for about a month now and usually, it is after work and when my family goes to sleep. It am enjoying it as a hobby and automating some of the tasks I am responsible for at work.
The big goal for me is taking the statement information I copy with VBA to a table and being able to create a batch report that then can be uploaded into a program I use at work, after that, well I have a few type of reports and tracking that I will eventually setup.
The code I have written does the following when I receive a statement, which can have up to 2000 transactions on it.
1. I run the VBA Code which lets me select the statement I want to load. The code checks to make sure the statement is not already on the Invoice Table. If it isn't, it then copies the details of the invoice to the Invoice Table.
2. The statement has a sheet called Data, which has the details of the invoice and all the transactions. I convert the range of transactions into a table and then copy the information I want to another workbook, which has two sheets; Invoice Database (which is a table to store the invoice details) and Transaction Database (which has a table to store all the transactions). This code works great. It is the next step I am not 100% sure how to proceed or what the best way is to proceed. The next step will only be dealing with the transaction table, however, the invoice table is how I am going to keep track that certain tasks have been done. i.e. the statement has been receipted, an email has been sent to the banking team to reconcile the deposit, a batch report has been created.
The batch report is what I would like to complete as it would assist my team in not being behind some of the actions and will free me up to handle other responsibilities. The batch report will upload to the program I use and update hundreds of files in a matter of minutes.
I know I can likely use code I wrote and was helped with that consolidated the transactions for manual receipting, but the code was written before I started learning about tables, which was a recommendation that was made to me.
The below is a mockup of a transaction spreadsheet, with just two short invoices.
The claim number is a unique ID but can appear a few times in one statement as it depends on how often the customer pays in that invoice period. It can also only appear once.
Below is an example using the claim number 4625WCE
So, what I want to do is consolidate the transactions per invoice, I should then only see 4625WCE twice in a temporary table.
I have looked at pivot tables, but I don't fully understand them and I am not sure it will do what I want, which is to create a temporary table to be set up automatically when I click a button to create the report.
I do have an array code that was written for me that I use when sorting a statement for receipting. The array code will consolidate the transactions.
Is there a better way to consolidate the table? If not, then how would I modify it so it only consolidates the transactions by an invoice number and then claims number?
Then is there a way that I can then take the above temp table to create my batch report, which is only the following, but combines info from the table? The batch report will combine each claim number into one row.
Below is an example of what I am aiming for.
If a claim is number with transactions is on 10 invoices then totals will be summed together and all the invoices numbers would be listed in the dairy note.
Alternatively, I will have an entry for each invoice number and claim number, which is fine as I am hoping to stay within a 30 day period and shouldn't see a claim number appear on multiple statements.
Anyway, I am sorry this ended up being so long
, but appreciate any help or pointers to help me go into the right direction.
I want to thank everyone who has helped me in past. I have only been ready and learning about VBA for about a month now and usually, it is after work and when my family goes to sleep. It am enjoying it as a hobby and automating some of the tasks I am responsible for at work.
The big goal for me is taking the statement information I copy with VBA to a table and being able to create a batch report that then can be uploaded into a program I use at work, after that, well I have a few type of reports and tracking that I will eventually setup.
The code I have written does the following when I receive a statement, which can have up to 2000 transactions on it.
1. I run the VBA Code which lets me select the statement I want to load. The code checks to make sure the statement is not already on the Invoice Table. If it isn't, it then copies the details of the invoice to the Invoice Table.
2. The statement has a sheet called Data, which has the details of the invoice and all the transactions. I convert the range of transactions into a table and then copy the information I want to another workbook, which has two sheets; Invoice Database (which is a table to store the invoice details) and Transaction Database (which has a table to store all the transactions). This code works great. It is the next step I am not 100% sure how to proceed or what the best way is to proceed. The next step will only be dealing with the transaction table, however, the invoice table is how I am going to keep track that certain tasks have been done. i.e. the statement has been receipted, an email has been sent to the banking team to reconcile the deposit, a batch report has been created.
The batch report is what I would like to complete as it would assist my team in not being behind some of the actions and will free me up to handle other responsibilities. The batch report will upload to the program I use and update hundreds of files in a matter of minutes.
I know I can likely use code I wrote and was helped with that consolidated the transactions for manual receipting, but the code was written before I started learning about tables, which was a recommendation that was made to me.
The below is a mockup of a transaction spreadsheet, with just two short invoices.
| Claim Number | Recovery Agent | Agent's Ref | Portfolio Code | Third-Party | Invoice Number | Invoice Date | Amount | Ex Commission | GST | Inc Commission | Net Amount | File Reference 2 |
| 4625WCE | Wayne Enterprise | 55122 | WEAI | Customer 1 | TX7282 | 1-31-2022 | 50.00 | 6.25 | 0.63 | 6.88 | 43.12 | |
| 6754WCE | Wayne Enterprise | 89994 | WEAI | Customer 2 | TX7282 | 1-31-2022 | 100.00 | 12.50 | 1.25 | 13.75 | 86.25 | |
| 6524WCE | Wayne Enterprise | 85378 | WEAI | Customer 3 | TX7282 | 1-31-2022 | 20.00 | 2.50 | 0.25 | 2.75 | 17.25 | |
| 4625WCE | Wayne Enterprise | 55122 | WEAI | Customer 1 | TX7282 | 1-31-2022 | 20.00 | 2.50 | 0.25 | 2.75 | 17.25 | |
| 6548WCE | Wayne Enterprise | 54782 | WEAI | Customer 5 | TX7282 | 1-31-2022 | 50.00 | 6.25 | 0.63 | 6.88 | 43.12 | |
| 4625WCE | Wayne Enterprise | 55122 | WEAI | Customer 1 | TX7282 | 1-31-2022 | 50.00 | 6.25 | 0.63 | 6.88 | 43.12 | |
| 6808WCE | Wayne Enterprise | 54866 | WEAI | Customer 7 | TX7282 | 1-31-2022 | 50.00 | 6.25 | 0.63 | 6.88 | 43.12 | |
| 6050WCE | Wayne Enterprise | 82822 | WEAI | Customer 8 | TX7282 | 1-31-2022 | 20.00 | 2.50 | 0.25 | 2.75 | 17.25 | |
| 5208WCE | Wayne Enterprise | 54771 | WEAI | Customer 9 | TX7282 | 1-31-2022 | 20.00 | 2.50 | 0.25 | 2.75 | 17.25 | |
| 6808WCE | Wayne Enterprise | 54866 | WEAI | Customer 7 | TX7282 | 1-31-2022 | 50.00 | 6.25 | 0.63 | 6.88 | 43.12 | |
| 6808WCE | Wayne Enterprise | 54866 | WEAI | Customer 7 | TX7282 | 1-31-2022 | 100.00 | 12.50 | 1.25 | 13.75 | 86.25 | |
| 6053WCE | Wayne Enterprise | 55063 | WEAI | Customer 12 | TX7282 | 1-31-2022 | 20.00 | 2.50 | 0.25 | 2.75 | 17.25 | |
| 4834WCE | Wayne Enterprise | 54541 | WEAI | Customer 13 | TX7282 | 1-31-2022 | 20.00 | 2.50 | 0.25 | 2.75 | 17.25 | |
| 5568WCE | Wayne Enterprise | 59998 | WEAI | Customer 14 | TX7282 | 1-31-2022 | 100.00 | 12.50 | 1.25 | 13.75 | 86.25 | |
| 6900WCE | Wayne Enterprise | 85377 | WEAI | Customer 4 | TX7282 | 1-31-2022 | 50.00 | 6.25 | 0.63 | 6.88 | 43.12 | |
| 5729WCE | Wayne Enterprise | 85378 | WEAI | Customer 16 | TX7282 | 1-31-2022 | 20.00 | 2.50 | 0.25 | 2.75 | 17.25 | |
| 6900WCE | Wayne Enterprise | 85377 | WEAI | Customer 4 | TX7282 | 1-31-2022 | 30.00 | 3.75 | 0.38 | 4.13 | 25.87 | |
| 5774WCE | Wayne Enterprise | 85701 | WEAI | Customer 18 | TX7282 | 1-31-2022 | 50.00 | 6.25 | 0.63 | 6.88 | 43.12 | |
| 6900WCE | Wayne Enterprise | 85377 | WEAI | Customer 4 | TX7282 | 1-31-2022 | 10.00 | 1.25 | 0.13 | 1.38 | 8.62 | |
| 6999WCE | Wayne Enterprise | 84664 | WEAI | Customer 20 | TX7282 | 1-31-2022 | 25.00 | 3.13 | 0.31 | 3.44 | 21.56 | |
| 5951WCE | Wayne Enterprise | 84664 | WEAI | Customer 21 | TX7282 | 1-31-2022 | 25.00 | 3.13 | 0.31 | 3.44 | 21.56 | |
| 6900WCE | Wayne Enterprise | 85377 | WEAI | Customer 4 | TX7282 | 1-31-2022 | 20.00 | 2.50 | 0.25 | 2.75 | 17.25 | |
| 4625WCE | Wayne Enterprise | 55122 | WEAI | Customer 1 | TX3662 | 3-17-2022 | 1969.00 | 492.25 | 49.23 | 541.48 | 1427.53 | |
| 6754WCE | Wayne Enterprise | 89994 | WEAI | Customer 2 | TX3662 | 3-17-2022 | 1964.00 | 491.00 | 49.10 | 540.10 | 1423.90 | |
| 6524WCE | Wayne Enterprise | 85378 | WEAI | Customer 3 | TX3662 | 3-17-2022 | 1199.00 | 299.75 | 29.98 | 329.73 | 869.28 | |
| 4625WCE | Wayne Enterprise | 55122 | WEAI | Customer 1 | TX3662 | 3-17-2022 | 1102.00 | 275.50 | 27.55 | 303.05 | 798.95 | |
| 6548WCE | Wayne Enterprise | 54782 | WEAI | Customer 5 | TX3662 | 3-17-2022 | 43.00 | 10.75 | 1.08 | 11.83 | 31.18 | |
| 4626WCE | Wayne Enterprise | 55122 | WEAI | Customer50 | TX3662 | 3-17-2022 | 1550.00 | 387.50 | 38.75 | 426.25 | 1123.75 | |
| 6808WCE | Wayne Enterprise | 54866 | WEAI | Customer 7 | TX3662 | 3-17-2022 | 1504.00 | 376.00 | 37.60 | 413.60 | 1090.40 | |
| 6050WCE | Wayne Enterprise | 82822 | WEAI | Customer 8 | TX3662 | 3-17-2022 | 709.00 | 177.25 | 17.73 | 194.98 | 514.03 | |
| 5208WCE | Wayne Enterprise | 54771 | WEAI | Customer 9 | TX3662 | 3-17-2022 | 1723.00 | 430.75 | 43.08 | 473.83 | 1249.18 | |
| 6808WCE | Wayne Enterprise | 54866 | WEAI | Customer 7 | TX3662 | 3-17-2022 | 1996.00 | 499.00 | 49.90 | 548.90 | 1447.10 | |
| 6808WCE | Wayne Enterprise | 54866 | WEAI | Customer 7 | TX3662 | 3-17-2022 | 1297.00 | 324.25 | 32.43 | 356.68 | 940.33 | |
| 6053WCE | Wayne Enterprise | 55063 | WEAI | Customer 12 | TX3662 | 3-17-2022 | 1455.00 | 363.75 | 36.38 | 400.13 | 1054.88 | |
| 4834WCE | Wayne Enterprise | 54541 | WEAI | Customer 13 | TX3662 | 3-17-2022 | 1508.00 | 377.00 | 37.70 | 414.70 | 1093.30 | |
| 5568WCE | Wayne Enterprise | 59998 | WEAI | Customer 14 | TX3662 | 3-17-2022 | 492.00 | 123.00 | 12.30 | 135.30 | 356.70 | |
| 6900WCE | Wayne Enterprise | 85377 | WEAI | Customer 4 | TX3662 | 3-17-2022 | 1805.00 | 451.25 | 45.13 | 496.38 | 1308.63 | |
| 5729WCE | Wayne Enterprise | 85378 | WEAI | Customer 16 | TX3662 | 3-17-2022 | 1535.00 | 383.75 | 38.38 | 422.13 | 1112.88 | |
| 6900WCE | Wayne Enterprise | 85377 | WEAI | Customer 4 | TX3662 | 3-17-2022 | 671.00 | 167.75 | 16.78 | 184.53 | 486.48 | |
| 5774WCE | Wayne Enterprise | 85701 | WEAI | Customer 18 | TX3662 | 3-17-2022 | 1074.00 | 268.50 | 26.85 | 295.35 | 778.65 | |
| 6900WCE | Wayne Enterprise | 85377 | WEAI | Customer 4 | TX3662 | 3-17-2022 | 1294.00 | 323.50 | 32.35 | 355.85 | 938.15 | |
| 6999WCE | Wayne Enterprise | 84664 | WEAI | Customer 20 | TX3662 | 3-17-2022 | 706.00 | 176.50 | 17.65 | 194.15 | 511.85 | |
| 5951WCE | Wayne Enterprise | 84664 | WEAI | Customer 21 | TX3662 | 3-17-2022 | 516.00 | 129.00 | 12.90 | 141.90 | 374.10 | |
| 6900WCE | Wayne Enterprise | 85377 | WEAI | Customer 4 | TX3662 | 3-17-2022 | 379.00 | 94.75 | 9.48 | 104.23 | 274.78 | |
The claim number is a unique ID but can appear a few times in one statement as it depends on how often the customer pays in that invoice period. It can also only appear once.
Below is an example using the claim number 4625WCE
| Claim Number | Recovery Agent | Agent's Ref | Portfolio Code | Third-Party | Invoice Number | Invoice Date | Amount | Ex Commission | GST | Inc Commission | Net Amount | File Reference 2 |
| 4625WCE | Wayne Enterprise | 55122 | WEAI | Customer 1 | TX7282 | 1-31-2022 | 50.00 | 6.25 | 0.63 | 6.88 | 43.12 | |
| 4625WCE | Wayne Enterprise | 55122 | WEAI | Customer 1 | TX7282 | 1-31-2022 | 20.00 | 2.50 | 0.25 | 2.75 | 17.25 | |
| 4625WCE | Wayne Enterprise | 55122 | WEAI | Customer 1 | TX7282 | 1-31-2022 | 50.00 | 6.25 | 0.63 | 6.88 | 43.12 | |
| 4625WCE | Wayne Enterprise | 55122 | WEAI | Customer 1 | TX3662 | 3-17-2022 | 1969.00 | 492.25 | 49.23 | 541.48 | 1427.53 | |
| 4625WCE | Wayne Enterprise | 55122 | WEAI | Customer 1 | TX3662 | 3-17-2022 | 1102.00 | 275.50 | 27.55 | 303.05 | 798.95 | |
So, what I want to do is consolidate the transactions per invoice, I should then only see 4625WCE twice in a temporary table.
I have looked at pivot tables, but I don't fully understand them and I am not sure it will do what I want, which is to create a temporary table to be set up automatically when I click a button to create the report.
| Claim Number | Recovery Agent | Agent's Ref | Portfolio Code | Third-Party | Invoice Number | Invoice Date | Amount | Ex Commission | GST | Inc Commission | Net Amount | File Reference 2 |
| 4625WCE | 55122 | Customer 1 | TX7282 | 1-31-2022 | 120.00 | 15.00 | 1.51 | 16.51 | 103.49 | |||
| 4625WCE | 55122 | Customer 1 | TX3662 | 3-17-2022 | 3071.00 | 767.75 | 76.78 | 844.53 | 2226.48 | |||
I do have an array code that was written for me that I use when sorting a statement for receipting. The array code will consolidate the transactions.
VBA Code:
Sub NMCopyTransactions()
'Dim DatabaseBook As Workbook, DataSh As Worksheet, ws As Worksheet
Dim LO_src As ListObject, DBR As Range 'everything of your 1st listobject
Dim LOH_dest_LC As ListColumn, iCol As Long 'evrything of your 2nd listobject
Dim MyColumn As Variant, bFlag As Boolean, cDest As Range, MyOtherColumn As String
'your workbook and worksheet
'Set DatabaseBook = Workbooks.Open(Filename:="G:\\Invoice_Database_Sheet.xlsx") 'the data is in this workbook
'Set DataSh = DatabaseBook.Sheets("Transactions_Database") 'and within this sheet
'your source
Set LO_src = Closedws.ListObjects("Transactions_Table") 'your "source" listobject
'Set LO_dest = Lo_Dest_Trans.ListObjects("TransactionsDatabase") 'your "destination" listobject
For Each MyColumn In Array("FILCLIREF1", "FILCLIREF1STRDESC", "STRPAYTAMT")
'what column in the 2nd listobject corresponds with a name in the 1st listobject
Select Case MyColumn 'if you get an error here, change MyColumn into cStr(myColumn)
Case "FILCLIREF1": MyOtherColumn = "Claim Number"
Case "FILCLIREF1STRDESC": MyOtherColumn = "Third-Party"
Case "STRPAYTAMT": MyOtherColumn = "Amount"
End Select
Set DBR = LO_src.ListColumns(MyColumn).DataBodyRange 'source = the databodyrange of that listcolumn, also here change in case of ... MyColumn into cStr(MyColumn)
'your destination
Set LO_dest_LC = loTranTable.ListColumns(MyOtherColumn) 'this listcolumn in that listobject
iCol = LO_dest_LC.Range.Column - loTranTable.Range.Column ' this is OFFSET for the x-th column of that table
'your copy
If Not bFlag Then 'the 1st loop, you assign cDest as the range if the column was the 1st column of your table
Set cDest = loTranTable.ListRows.Add.Range.Cells(1, 1).Resize(DBR.Rows.Count) '
bFlag = True 'set flag for the next loops
End If
cDest.Offset(, iCol).Value = DBR.Value 'OFFSET to the 1st listcolumn of that table
Next
End SubIs there a better way to consolidate the table? If not, then how would I modify it so it only consolidates the transactions by an invoice number and then claims number?
Then is there a way that I can then take the above temp table to create my batch report, which is only the following, but combines info from the table? The batch report will combine each claim number into one row.
Below is an example of what I am aiming for.
| Claim Number | Diary Note | Gross Amount | Inc Commission |
| 4625WCE | Payment(s) have been received on Invoice(s) TX7282 and TX3662 for the amount of $3,191.00. There is a commission charge of $2329.97 | 3191.00 | 861.035 |
If a claim is number with transactions is on 10 invoices then totals will be summed together and all the invoices numbers would be listed in the dairy note.
Alternatively, I will have an entry for each invoice number and claim number, which is fine as I am hoping to stay within a 30 day period and shouldn't see a claim number appear on multiple statements.
| Claim Number | Diary Note | Gross Amount | Inc Commission |
| 4625WCE | Payment has been received on Invoice TX7282 for the amount of $120.00. There is a commission charge of $16.51. | 120.00 | 16.51 |
| 4625WCE | Payment has been received on Invoice TX3662 for the amount of $3071. There is a commission charge of $844.52 | 3191.00 | 844.52 |
Anyway, I am sorry this ended up being so long
, but appreciate any help or pointers to help me go into the right direction.
