Excel VBA Loop to Format General Ledger

Hello,

In the attached workbook, I am trying to take the sheet "GL Format 1" and turn it into the format of "GL Format 2" through a macro. I am not sure how I can do this because the number of lines will vary for each account based off how much activity (increased or decreased) and this will affect where formatting goes. Can anyone please help with formatting all of "GL Format 1" into "GL Format 2" with a macro? I need each account to have all activity for the period in it, i.e., 201701-201706 activity for account 1012-000 would be 22 lines long when formatted into "GL Format 2" with a total of $(422.01). I included some activity for 2 accounts on "GL Format 2" for a reference on the format needed.

Any help on this would be GREATLY appreciated! Thank you!

Try

Please Login or Register  to view this content.

Hi Jindon,

It looks like it worked, but only for the 201701 period and not all periods, so it would need some sort of code to grab all Periods listed. Also I need the macro to do a Total of each account two rows below the last line item for each account, i.e., Total of H:H in cell H9 for 1012-000, total in cell H27 for 1110-000 and so on, but the placement of the the Total will vary each time. I saved a new workbook and highlighted the differences in yellow from "Sheet1" to "GL Format 2" to show you the difference.

Thank you

Forgot to add workbook. Here it is.

Originally Posted by Hoover5896

Hi Jindon,

It looks like it worked, but only for the 201701 period and not all periods, so it would need some sort of code to grab all Periods listed.

It should list all and I see in the result.

Please Login or Register  to view this content.

It looks like the code got the total added, but the period is still only coming out to be 201701 instead of all activity (201701-201712 so new data can be dropped in) As of now the workbook only has 201701-201706 though.

Where did row 9 of "GL Format 2" that is the period of "2452/3/28" come from?

There's no such data in "GL Format 1".

I'm totally lost.

I don't understand the difference between what you are asking and the last code I have posted.

I attached another workbook to show the difference. Example being account 1012-000 showing up 7 times each for a different period and this is happening for all accounts, whereas this account should just be listed once with all seven periods worth of activity under it. That way there is only 1 account being listed no matter how many periods get added.

I made 2 tabs to show the difference between the "Current Macro" and what I am needing listed as "All Periods Under One Account" tab.

Thanks for your patience on this!

Change to

Please Login or Register  to view this content.

This is perfect!