Automate my bank statements - Making it dynamic

Question

I'm trying to create a macro that categorizes my bank statements. But I'm not that proficient with VBA and I don't have a clear idea on how to tackle this problem.

It goes as follows:

I import a CSV into excel which I delimit into columns. This part is working now. Looking like this:

I want to be able to cut the relevant transactions and paste them to their respective tables. So municipality goes to the municipality table, bank goes to the bank table, energy goes to the energy table.
I am using different banks so therefore I have different CSVs. So the layout changes all the time. Some banks have Date in column A, some in column C. So the ranges should always be dynamic.
I created a Key Table that has all the relevant variables that can match with the relevant transactions looking like this:

The Key can be found somewhere in the Imported range. Like I said before, the location of the key changes depending on the bank. I want to be able to say the following.

If key x matches somewhere in import_range then cut "Name, Description and Amount from the same row into another table on another worksheet which correlates to the Type of the Key.

So from the image, if key 21315665 is found (as a string?) somewhere in import_range then I want the Name (internet provider), Description and Amount that correlates to that same row cut and pasted into a different (already existing) table, adding the information to the bottom of said table.

I hope anyone can help me out with this. I have a working code that I wrote but it is a pain to update and very basic. It just compares if key x is in cell z and if so copies it.

Dim myString As String
Dim incassovalue As String
Dim lastrow As Range
Dim Table As ListObject

Set Table = Sheets("Transactions").ListObjects("Energy")

Import_last_row = Sheets("Import").Range("A" & Rows.Count).End(xlUp).Row

For i = Import_last_row To 6 Step -1

'Energy
    myString = Sheets("Import").Cells(i, 9).Value
    incassovalue = Sheets("Incasso").Cells(5, 5).Value

    If InStr(myString, incassovalue) > 0 Then
        If Table.ListRows.Count > 0 Then
            Set lastrow = Table.ListRows(Table.ListRows.Count).Range
        If Application.CountBlank(lastrow) < lastrow.Columns.Count Then
            Table.ListRows.Add
        End If
    End If

    If Table.ListRows.Count = 0 Then
        Table.ListRows.Add Position:=1
        Set lastrow = Table.ListRows(1).Range
    Else
        Set lastrow = Table.ListRows(Table.ListRows.Count).Range
    End If

    Sheets("Import").Cells(i, 1).Copy Table.Range([Energy].Rows.Count + 1, 1)
    Sheets("Import").Cells(i, 2).Copy Table.Range([Energy].Rows.Count + 1, 2)
    Sheets("Import").Cells(i, 7).Copy Table.Range([Energy].Rows.Count + 1, 3)
    Sheets("Import").Cells(i, 1).EntireRow.Delete

    End If
Next i

Now this code kind of works, but it isnt dynamic at all, I would have to make a 1000 conditions like this to make it work for all my bank accounts. I hope someone here can help me out, or send me in the right direction.

Automate my bank statements - Making it dynamic

0 Answers