Need a macro to create a Trial balance template

[mudani]

Hi, My name is Milinda Udani. I am a senior accountant. I need help with creating a trial balance for FCL who sends us their financials by Dr. and Cr. My job is to create a trial balance and transfer FCL's GL to my company's GL. I am attaching a sample file here. I need a macro which transfer and match the FCL GL from FCL mapping where all the coding is provided with their financial and bring it to the trial balance tab. here is my thought process

1) I have total 4 tabs(1)JE (where I book the entry), (2) FCL-Trial balance (3)FCL-Financial (4)FCL Mapping
2) I need tab 2(Trial balance) where all the calculation happens
3) FCL-financial is provided by the company every month
4) FCL-mapping is any new coding is added from monthly financial

Also, once the monthly trial balance is created. I also want a macro where it create a new blank template where previous month's trial balance number is copied and paste as value.

I will greatly appreciate a help with macro.

 

[Marc L]

Hi, according to beginner level Excel basics the balance should be achieved with few VBA codelines with at least a smart attachment,​

your attachment contains errors even a beginner accountant can't make …​

​

First, as the Account ID must be unique so check and correct the FCL Mapping worksheet like your result Sample Outcome as well ‼​

No duplicate Account ID whatever the worksheet …​

​

You must also create a Previous Month worksheet where you must​

• copy the entire Account ID column from the FCL Mapping worksheet to cell A1
• fill column B with the previous month Closing Balance, values only.

You may also rename Sample Outcome headers :​

• F2 as Closing Balance
• H2 as Opening Balance
• I2 as Activity.

Once the worksheets are corrected, created and filled then attach the expected correct workbook saved as binary format .xlsb …​

​

 

[mudani]

Sure, will do. Thank you!

 

[Marc L]

Without knowing the previous month closing balance it could be achieved computing a reverse balance​

if only JE worksheet is correct like accordingly FCL Mapping as well but,​

according to your attachment, two JE accounts are missing in FCL Mapping !​

​

As accoutancy is nothing but accuracy, if you really are an accoutant then that's easy for you to explain us​

step by step what to do according to the poor worksheets design …​

​