How to Transfer data from General Journal to General Ledger using VBA

[Zubeee]

I am trying to set up a General Journal and Ledger in Excel. Right now, I am manually posting the dates and the numbers into the ledger. Ideally, every general journal transaction would automatically "post" to the Ledger as soon as it is inserted into the General Journal. But, I don't know how to do that!

Any suggestions on how to get the General Journal to automatically post to the Ledger (using vlookup, etc.)?

[Pete_UK]

It would help if you attached a sample Excel workbook, by following the guidelines given in the yellow banner at the top of the screen.

Pete

[Pepe Le Mokko]

And BTW Ms Off version 20210? ( see your profile)

[Glenn Kennedy]

Will you be overwriting the old data in the Journal... or adding new data on at the end.

If you are over writing it, Excel can not remember the old values, using formulae. To do that you will need VBA. If that is what you want, shall I move the thread to the VBA forum?

[Zubeee]

Glenn Kennedy, Thanks for your sincere response. Yup, the point is that I want to transfer (Overwrite) data from G. Journal Sheet to G. Ledger Sheet. If the point can solve by using normal index match or Vlookup function or some other, so it will be much better as bcoz by profession I m an Accountant & don't know VBA Coding enough to operate it further. I know general excel formulas a bit.

Thanks in Advance....

[Glenn Kennedy]

As I said... Excel can NOT remember overwritten values, so if this is the way you want to go - you have no choice but to use VBA.


Shall I move it, or do you want to rethink your entire approach? I'll not be able to help with VBA (an analytical chemist... but VBA was not allowed in work, so I never learned it) - but others will help.

[Zubeee]

Glenn Kennedy, Thanks Sir for guiding. Yes please go with VBA to get solution.

[JohnTopley]

I am not an accountant so ..

Data in "Cash" and "Sales" is multi-company but the third table is company-specific e,g Yarn Source Limited (so multiple tables are required) ? or can we add an extra column with the company name ?

So how many entries can we expect and how many companies ? Are the tables in your sample files the EXACT format in your actual file(s) ?

Please post a realistic sample of data with correct table formats.

[Zubeee]

John Topley The reply is with thanks, referring to your Q#1: Yes We can add an extra column at left first or to the right (end, last column) but that column shouldn't be in printable reports. Furthermore, this column must be add to all tables (Ledgers) like "Cash", "Sales", "Yarn Source Ltd" & so on.....

referring to Q#2(i) These entries depend upon on sheet1 (G. Journals) which can be upto 1k or above. (ii) Yes these tables (Ledgers) are static (Exact Format) all over the world.

referring to Q#3: As I mentioned in Q#2 (ii) these are static, the data in tables (Ledgers) and tables (Ledgers) are actual correct.


Best Wishes......