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?

 

[HeadAgainstABrickWall]

Have you considered transforming it in Power Query? This will allow you to rename columns, remove unneeded, filter, replace values etc. Fully incorporatable into macros, and the output is a new table.

 

[Oberon70]

Have you considered transforming it in Power Query? This will allow you to rename columns, remove unneeded, filter, replace values etc. Fully incorporatable into macros, and the output is a new table.

No, I will have to google Power Query as it sounds like a good idea.

 

[Oberon70]

I need yo add that this table will have new entries added to the first blank row as I receive them

 

[HeadAgainstABrickWall]

No, I will have to google Power Query as it sounds like a good idea.

Data -> From table/range to open it. Very useful tool that doesn't get as much love as it should.

 

[HeadAgainstABrickWall]

Are you wanting to set up this spreadsheet and then update manually, or automate the whole process?

I need yo add that this table will have new entries added to the first blank row as I receive them

 

[Oberon70]

Are you wanting to set up this spreadsheet and then update manually, or automate the whole process?

This is going to be automated . I will have auserform will several options. A basic outline is:
1. New statement received. This will take the details of the statement and place into a table. This table will keep track if a batch report has been crested, a diary report, if the statement has been receipted. I have this info going into the table via a macro. It will also let me generate a weekly report on the status of receipting.
2. The transactions for the statement will go into the table_tranaactions. With this I will be able create a spreadsheet that it easier to manual receipt. Check if the statement has been receipted.

These are the two basic goals but there a a few smaller ones I will add on once the above is working.

Sorry, I hope that isn't confusing?

 

[HeadAgainstABrickWall]

This is going to be automated . I will have auserform will several options. A basic outline is:
1. New statement received. This will take the details of the statement and place into a table. This table will keep track if a batch report has been crested, a diary report, if the statement has been receipted. I have this info going into the table via a macro. It will also let me generate a weekly report on the status of receipting.
2. The transactions for the statement will go into the table_tranaactions. With this I will be able create a spreadsheet that it easier to manual receipt. Check if the statement has been receipted.

These are the two basic goals but there a a few smaller ones I will add on once the above is working.

Sorry, I hope that isn't confusing?

It sounds like the data entry will be automated? In which case no issue, Power Query outputs can be refreshed, applying the same steps to the entire table *as long as you don't add any columns*.

 

[Oberon70]

So, would I be using power query to move the information I want from one sheet to a table?

I am googling power queries and I think this is a bit more advance that what I can currently work with. It sounds great.

 

[HeadAgainstABrickWall]

So, would I be using power query to move the information I want from one sheet to a table?

I am googling power queries and I think this is a bit more advance that what I can currently work with. It sounds great.

Power Query will automatically add the transformed data to a new table in a new worksheet, it does not change the original data. It's like taking a photo of the data and applying filters - the thing you took the photo of doesn't change, but the way you see it does.