+ Reply to Thread
Results 1 to 6 of 6

VBA Macro to simplify large bank reconciliation

  1. #1
    Registered User
    Join Date
    01-12-2012
    Location
    PA
    MS-Off Ver
    Excel 2010
    Posts
    7

    VBA Macro to simplify large bank reconciliation

    Hello,

    Recently one of our bank accounts went from 10k+ transactions a month, to 15k+ transactions a month. Obviously this is far too large to click these transactions one by one in our accounting system (Quickbooks Enterprise), and unfortunately our bank does not have the ability to connect to auto reconcile, so what we do is create an OFX import file based on the bank statement and import it into QuickBooks. Since the 5k+ transaction increase, this does not seem to be working as well so I wanted to create a macro to simplify this.

    Basically after we import the OFX file, there are several transactions that are checked off to be reconciled that should not be, and vice versa. What we do is compare the bank statement transactions to the reconciled export list from Quickbooks to see where the discrepancies are. Currently we have someone go through these line by line, clicking off transactions that should not be checked, and clicking the ones that the import missed. What I would love to create (with the wonderful help from the members of this forum) is a macro that would go through these exports and create a simple list of transactions to un-reconcile and reconcile. I think the attached excel spreadsheet describes what I'm looking for much better than I can explain it here (highlighted items will not be highlighted on actual bank rec file).

    I consider myself a basic VBA user with the ability to understand and modify VBA coding, but really have a lot of trouble getting something started from scratch. If anyone could provide me coding for something similar to this that I can attempt to modify for our needs, that would be great. As always, I greatly appreciate any help I can get on this. Thank you.
    Attached Files Attached Files
    Last edited by signgain82; 08-19-2014 at 04:39 PM.

  2. #2
    Forum Expert mike7952's Avatar
    Join Date
    12-17-2011
    Location
    Florida
    MS-Off Ver
    Excel 2007, Excel 2016
    Posts
    3,519

    Re: VBA Macro to simplify large bank reconciliation

    Give this a try will work on your sample workbook.

    Please Login or Register  to view this content.
    Last edited by mike7952; 08-18-2014 at 06:49 PM.
    Thanks,
    Mike

    If you are satisfied with the solution(s) provided, please mark your thread as Solved.
    Select Thread Tools-> Mark thread as Solved.

  3. #3
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464

    Re: VBA Macro to simplify large bank reconciliation

    Try this
    Please Login or Register  to view this content.
    Attached Files Attached Files

  4. #4
    Registered User
    Join Date
    01-12-2012
    Location
    PA
    MS-Off Ver
    Excel 2010
    Posts
    7

    re: VBA Macro to simplify large bank reconciliation

    Thank you both very much!!! These both work wonderfully so far. This will surely save hours of time each month.

  5. #5
    Registered User
    Join Date
    09-28-2015
    Location
    England
    MS-Off Ver
    2013
    Posts
    1

    Re: VBA Macro to simplify large bank reconciliation

    Hi All Member

    I would like to create a Bank Management System in VBA or only Excel with VBA. Is there anyone can give me any idea? Because I'm not familiar using VBA.

    Thank you very much.~~~~

  6. #6
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464

    Re: VBA Macro to simplify large bank reconciliation

    happyvba

    Unfortunately your post does not comply with Rule 4 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.

    If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.

    Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. bank reconciliation.
    By elias jahangir in forum Excel General
    Replies: 3
    Last Post: 05-29-2014, 01:34 PM
  2. Bank Reconciliation
    By Accountant0607 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 02-24-2014, 10:32 PM
  3. Bank Reconciliation
    By kfoote in forum Excel Programming / VBA / Macros
    Replies: 22
    Last Post: 12-04-2013, 04:45 PM
  4. Bank Reconciliation VBA Help
    By showgun3 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 06-12-2013, 12:26 AM
  5. Bank Reconciliation
    By thameem127 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 05-21-2012, 03:17 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1