Thoughts on the best way to proceed to copy data to a table

[Oberon70]

This is part 2 to my previous post were I copied the invoice information to a table to list the invoices I receive. The next part is were I am going to only copy the columns I need from the data sheet on the statement.

The statements from different provides will have there data organised differently, which is fine. I likely will use case to ensure the correct process is selected.

The issue is it is not a 1 to 1 copy. I am only wanting specific columns. Below is a dummy Data Sheet. There is information above line 48, but I am only interested in the information from line 48.

The selected information would then be copied to the below table on another sheet/workbook.

I had written a code that would convert the above information into a table, but I didn't know if that would be the best way? where I copy each column I want into the above table?

The following columns I already have the information assigned in variables from a previous sub:
Provider, Portfolio Code, Invoice number and Invoice Date.

FileRef1 = claim number
Provider's Ref = FileCode
Amount = StrPaytAmt
ExCommission = Strcomamt
GST = STRGSTAMT

The above would be dynamic as different statements have different amount of transactions.

looking forward to any ideas?

 

[Oberon70]

It sounds great and I will defiantly look into it, but for now I am going to just copy specific columns to the 1st blank row in the following table and do that for each table I have. Well that is the plan.

I don't seem to have the right format to select the row to paste from. I need to have the basics up quickly so I can create some batch reports for another system to upload.

and get an error:

Sub CopyTransactions()

LastRowNum = FindLast(xlFindLastRow) + 1

Closedws.ListObjects("Transactions_Table").ListColumns("FILCLIREF1STRDESC").DataBodyRange.Copy


DBaseTrans.ListObjects("TransactionsDatabase").ListColumns("Customer").DataBodyRange.Row(LastRowNum).Paste


'Rows("2:2").Select


End Sub

It seems that DataBodyRange.Row(LastRowNum) doesn't work.

The error message is

 

[Oberon70]

Most of the night, I read up on the power query for excel, and it looks fantastic. However, I cannot figure out how to automate power query.

My goal is to have everything done by clicking buttons on a user form.
I want to be prompted to select the statement to be processed as I receive several statements each month with over 5,000.00 transactions. I then have code written that grabs the invoice details, which are not in one table, and code copies it into a table on my Database Workbook as long as the invoice is not already on the table. If the invoice is not on the table, the code will need to copy each row, but with only specific columns. I have four providers, and each provider's statement has the data in different formats. The macro identifies the provider and then runs the correct subs to copy the information to the Database Workbook. If anyone can provide me with examples of how I can do this? I am happy to try it out?

 

[HeadAgainstABrickWall]

Most of the night, I read up on the power query for excel, and it looks fantastic. However, I cannot figure out how to automate power query.

My goal is to have everything done by clicking buttons on a user form.
I want to be prompted to select the statement to be processed as I receive several statements each month with over 5,000.00 transactions. I then have code written that grabs the invoice details, which are not in one table, and code copies it into a table on my Database Workbook as long as the invoice is not already on the table. If the invoice is not on the table, the code will need to copy each row, but with only specific columns. I have four providers, and each provider's statement has the data in different formats. The macro identifies the provider and then runs the correct subs to copy the information to the Database Workbook. If anyone can provide me with examples of how I can do this? I am happy to try it out?

Macro recorder records Power Query actions. Once the query is established (can be done through an external query as suits) you can just set a "refresh connection and go to table" button.

That's the way I would do it anyway, but I'm a huge fan of relatively single-step processes where possible.