Connecting main file with statement of account

[Raikkonen]

Hello! in excel, is it possible to do this?


So in the main file i have the loading list, which includes a Waybill, Date Loaded, Supplier, the Customer, and the Estimated amount.

And in the Customer's SOA file, the Date, Waybill, Supplier, Estimated Amount.

Now, when we add information in the main file (Loading List), is there a way to automatically copy the Date, Waybill, Supplier and Estimated Amount, to their respective Customer SOA file? In that way we wont have to manually copy then paste information from the loading list, to the customer's SOA file.

I have uploaded some files, I hope it gets seen.

Im sorry if I cant make it clearer, English is not my first language. Thank you so much!

[p24leclerc]

Look at attached workbooks. I had to modify them a little bit to ease the process.
All the files MUST be in the same folder. That's the way the macro is programmed. If it is not the case, you'll have to specify the path manually by replacing the assignment of the variable "Act_Path" by the real one.
Here is the macro:

Please Login or Register  to view this content.

[Raikkonen]

Hi, Thank you for the response!

Sorry if this may sound stupid, but I downloaded them both, put them inside a folder, then i tried to test it, put information on the loading list, and then PALERMO PALMARES as customer, saved it, then i checked the SOA File, its still blank.

Am I doing something wrong? Do I need to put the macro somewhere?

Thank you so much for your time!

[p24leclerc]

First, you only need to have the workbook LOADING LIST to be opened. Close all the others.
Then you have to manually activate the macro named Transfer_To_Customer?
You can do this by clicking on the View menu and then on the Macro menu on the toolbar. It will show you all the macros available. Search for the macro nmaed Transfer_To_Customer and click on RUN.
Now, if you look at the Palermo file, you should see that the data were transfered.

[Raikkonen]

Amazing! It works perfectly!

I've been studying the code recently (changing the rows and columns in the code because the original loading list file is a bit different, its more messy, so I only uploaded a simplified version of it), and im trying to apply it to our original Loading List file, I've not gotten it to work yet, but I have found out two things.

First, When i run the macro again, it appends the information in the SOA File, which leads to info duplication, but i think i have an idea.

Second, I think when i will run the macro it will add all of the information in the Loading List to their respective SOA File, Which is a LOT, because every loading list file we have is good for one year, inside it, it is divided into months.

I think I have an idea that solves this both, I have made a new column on the most right, called "Unpaid", now is it possible, when i run the macro, it checks if there is a letter "x" (I guess it serves as a ticker), and whichever row has that "x" in the unpaid column gets its info (Date, Waybill, Supplier and Estimated Amount, same stuff as the previous) added to the customer's SOA. After that, it removes the "x" tick so it wont be their next time the macro runs.

Anyhow, you have been a massive help, I can't thank you enough!

Here is our original Loading List file, it has a lot of things already thats why I thought a ticker would be helpful

[p24leclerc]

There are lots if information in rows above each table. You are sure you don't need any of these copied in hte customer files.
Do you want the macro to create a customer file if it does not already exist? This might be a problem though as any mistake on a customer name will create a new file.
About the X flag in the unpaid column, I'd rather put it the other way around and make it a PAID column and only copy those that have no X in this column. The macro can put an X when the data is copied to the customer file. This way, it is less error prone. When you create a new WB no. the operator would not have to remember to put the X to say it was not paid. The computer will be more reliable here.

Let me know what you think of this.

[p24leclerc]

you can try the following macro which does about all you are asking for.
All customer files must be created first and placed in the same folder as the LOADING LIST.xlsm workbook.
As I mentionned in my previous post, the added column is named PAID and you leave it blank when you create the data in the LOADING LIST workbook. The macro will copy only those rows that have a blank cell in this column. Once tranfered to the Custoomer file, it will put an "x" in this column so it will not be duplicated.

Please Login or Register  to view this content.

[Raikkonen]

There are lots if information in rows above each table. You are sure you don't need any of these copied in hte customer files.

If you mean the CM NO., OPEN, ARRIVAL, etc., that is alright, we wouldnt need it in the SOA files

Do you want the macro to create a customer file if it does not already exist? This might be a problem though as any mistake on a customer name will create a new file.

Hmm, I think it is better if it doesn't automatically make a file, because I know they get the customer's name wrong sometimes, so its better if it just gives the run-time error of the file not found, Ill just provide them with an SOA template so they can rename it for the customer, and just run the macro again.

About the X flag in the unpaid column, I'd rather put it the other way around and make it a PAID column and only copy those that have no X in this column. The macro can put an X when the data is copied to the customer file. This way, it is less error prone. When you create a new WB no. the operator would not have to remember to put the X to say it was not paid. The computer will be more reliable here.

Actually, that's a better idea! I don't know why I haven't thought of that!


EDIT: Oops, havent refreshed the page for a while and did not see your new post, Ill check it out!

EDIT 2: It works flawlessly!

It worked great paired with the SOA file you gave me on your first post. But after I have added stuff below the table on the SOA file (you can check it out), when i run the ran the macro and check my SOA file, it goes below the table, why is that? I hope you can help me set this SOA file up (Its the template file)

[p24leclerc]

the problem is that you wrote in column "A" below the cell with DATE: in it.
The way the macro works, it starts at the bottom of the column "A" and does a "END-UP" to find the first non-empty row. You must not put any data in column A below the DATE: title.

see the attached worbook that you can use as a template. The only changes are:
1. Move the non-empty cells from column A to columnm B
2. Change the function in cell D12 from SUM(D10:D11) to SUM(D9:D11). This way it will expand as the macro adds rows in the sheet.

I also changed the macro so it will always remove the BOLD font the first time a row is added on a customer sheet. It does not affect its operation at all. here it is:

Please Login or Register  to view this content.

[Raikkonen]

Oh! I get it, thanks!

Alright I have copied the code and modified it a bit, I created a folder called "SOA" within the Loading List's folder so it doesn't get messy because of all the SOA files, so i changed the "/" to "/SOA/". Everything seems to be working fine! Thank you!

Can I request something one last time? (This would be another macro) I have also created a folder called "LEDGER" within the Loading list's folder, and the this one is similar to the previous macro, but this time it copies WB NO., D.LOAD, QTY, SUPPLIER, AR#, CBM, AMOUNT, and OR#. So it would just find the customer's file inside "/LEDGER/" (this time the end of the filename is LEDGER instead of SOA).

I was thinking I would add another column after the PAID column in the Loading List, called "ADDED TO LEDGER" which will also have an "X" mark when it gets added to prevent duplication (similar to the SOA macro)

I know this is too much to ask, but I don't know where to turn to!

Anyways, Thank you so much!

The ledger template looks like this by the way

[p24leclerc]

here is the LEDGER macro.

Please Login or Register  to view this content.

Would you please stop adding REP to my account? I'm getting nervous. I know you appreciate it but you should not do this. Thanks anyway.

[Raikkonen]

My sincere apologies, it's just that I do not know any other way to repay you.

The ledger macro is working perfectly, thanks for also putting comments on all of your macros, its really helpful!

Anways I can't stress it enough, thank you so much sir, god bless you!

[p24leclerc]

it's my pleasure.
Regards