Creating a Chart of Accounts from a General Ledger in Microsoft Excel using VBA

Recently I was given the task of extracting a chart of accounts (COA) from a general ledger report. There are many ways I could have done this. Using Auto Filter comes to mind. Being me, I chose to write a small program in Visual Basic for Applications (VBA). In this post I will walk you through the design and code of the program I made.

General Ledger

First we need to determine the goals for our output or in this case the chart of accounts. I needed a report with:

  • account number
  • account description

In the source file we see that the account number is in Column A and the account descriptions are in Column E. We also see that rows that begin with a number are the rows that have the account number/description. Since that’s all we need we’ll ignore everything else.

With that information we know that our solution will have to do the following:

  • find or determine the first and last rows on the source worksheet
  • examine each row in the source worksheet to see if it starts with a number
  • if it does start with a number then
  •     take the account number and
  •     take the account description and
  •     write them to the destination sheet
  •     lastly increment the row in the destination worksheet

It’s obvious that we need a loop in this case we’ll use a FOR loop to examine each row of the source worksheet. It’s also clear we’ll need an IF statement to determine if the first character of the row is a number or not.

Sub ExtractCOA()
    'This program will extract the chart of accounts from a Yardi 7
    'default General Ledger (analytics) report run to Excel 
    'Derek Harlan April 2018 - Licensed in the Public Domain 

    'Setup Object Variables for the worksheets 
    'src will be the Source worksheet 
    Dim src As Worksheet 
    'dest will be the Destination worksheet 
    Dim dest As Worksheet 
    'Setup some counter variables 
    'firstRow / lastRow will be the first and last rows of the Source workbook 
    'nextRow will be the row to write in the Destination workbook 
    'i will be the counter in the For loop 
    Dim startRow as Long 
    Dim lastRow As Long 
    Dim i As Long 
    Dim nextRow As Long 
    'Set the initial values for the variables 
    Set src = Sheets("Report1") 
    Set dest = Sheets("Sheet1") 
    startRow = 6 ' this is easy to determine so I just set it the row number 
   'Find the last row by searching the workbook from the bottom row up 
   'and assign it to the variable 
    lastRow = src.Range("A1048576").End(xlUp).Row 
    i = 0 
    nextRow = 2 'Row 1 will be the headers in the worksheet so Row 2 is where the data starts 
    'We'll loop through each row between the first and last rows of the Source workbook 
    For i = startRow To lastRow Step 1 
       'If the first character of the value of the cell in A and the current row (i) is a number then 
       'that row has the account number and name on it 
        If IsNumeric(Left(src.Range("A" & i).Value, 1)) Then 
            'set the values in the destination worksheet from the values on the 
            'current row of the source worksheet 
            dest.Range("A" & nextRow).Value = _ 
               src.Range("A" & i).Value 
            dest.Range("B" & nextRow).Value = _ 
               src.Range("E" & i).Value 
            'lastly increment the nextRow in the destination worksheet 
             nextRow = nextRow + 1 
        End If 
    Next i 
End Sub 

Running it creates this output –

Chart of Accounts in Excel

With both of the requirements met I can say this was a success!

This project took about 20 minutes, from start to finish. In this instance I’m not sure if I saved any time however should I need to do this again I already have the program ready. When evaluating alternatives think about how often you’ll be asked to do this. It may take time to come up with the initial program but once you have it, you see a colossal increase in productivity!

Do you want to get started with VBA? Or want to learn what a FOR loop is?

Check out my book for more uses of Excel VBA in your daily work:

Beginning Microsoft Excel VBA Programming for Accountants

available on Amazon, Apple iBooks and other eBook retailers!

Questions or comments? Connect with me on



New ways of thinking vital for accountants, but so are old-school values

Good Day All,

In this article Amato describes how Accountants need to blend “old school” and “new school thinking”. He boils it down to a concept called anticipatory thinking or integrating current trends with your clients practices. For example he relates that an accountant who arrives at a meeting with paper files and folders would be perceived as out of date and not working as efficiently as they could be. So he suggests that accountants actively work to move clients forward, technologically speaking, and show them the benefits of “new thinking”.

Currently I work in a paperless company. So I’ve come to appreciate how that second monitor and Adobe Acrobat make life so much better. I also prefer electronic invoice presentment (EIP) over paper invoices. So I don’t really disagree with Amato here, but I don’t like the idea of automatically discounting an “old way”. In his view, the “blending” is simply retaining the values of old (Honesty, follow through) and using a tablet to present your reports. While these values are important and should be retrained, I don’t believe the methodology should be so easily forsaken. Followers of my site know I always look for new ways to do things, but I try to first understand why the “old way” came about. Sometimes, it hides an important step or rational which might not be obvious especially if the team has become complacent in the process.

One particular Gem is his idea of a “Pre-mortem” before taking on a project. Thinking about how you’ll work it and what could potentially go wrong and how to address these concerns. This is a crucial skill for accountants, and one that we should practice more often. For example before I begin a close, I have a standard checklist of all basic tasks (for example to  reclass existing prepaids, check for new prepaids, record bank fees,  etc.) and then I add any special client requirements (for example a particular work paper or maybe some kind of consolidation entry) and then I try to best plan the timing on these so as to minimize my stress and avoid error.

Do you pre-mortem in your firm or workday? Let me know in comments.


Source: New ways of thinking vital for accountants, but so are old-school values