Excel Automation

Messy spreadsheets limit Excel’s power – data in various worksheets or workbooks makes it hard to combine, re-use, sum, lookup, to use Pivot Tables, to be interactive and hard to update.  Better structure & maximizing the use of Tables for dynamic updating is a key automation success.

Sometimes, printing takes too long because there are columns or rows to be hidden or multiple ranges to be printed or of course the range to be printed is dynamic or different each time.  A print macro can do all that setting up for you in a blink.  

Or you or your staff are re-keying data from your cashbook spreadsheet to your Accounting software – Excel can convert data from one format to another in the columns and format your Accounting software like MYOB or Xero needs for import.  

A macro can even save this data output range where you want it as a .csv or text file ready to import into your software.  

I used to enter quarterly data into MYOB for each BAS for the Dental Practice we owned and this approach cut a horrible 4 hour job into 10 minutes.  The conversion range also included some key checks to minimize the risk of trying to import data with errors.

Data should be entered once and re-used where ever it’s needed so stop re-keying or copying and pasting the same data in multiple places across worksheets or
workbooks.  Better structure, use of Tables, lookups & Power Query or VBA alleviate this.

Updating data like a new month’s actuals, a new supplier price list, new exchange rates, new forecasts – these tasks occupy a lot of what any analyst or manager does in a day.  Or used to.  So much can be done now with Excel Tables and Power Query or VBA.

Prices, part numbers, product descriptions, disclaimer clauses whatever is tied to a key field like a product number.  Use of Tables & lookups solves much of this.

Keying data to other software like your accounting package is a massive time waster.  Power Query or VBA can extract data, rearrange it to the needs of your other software & be imported via a .csv or .txt file from a set folder

I probably don’t have to explain using a centrally maintained template for users is obviously more efficient and is more efficient for improvements and fixes.  But also in VBA, it’s often better to have the code copy from an easily maintained template to the final output whether that be new workbooks, worksheets, pivot tables or pivot charts together with all the formulas, data validation and formats needed.  I once did a Budget application where over 100 branches needed to input their budget.  The final workbook just had a template sheet and the VBA code and few buttons to create all the input worksheets (in stand alone workbooks), pre-filled from data in a Table and saved to a defined folder each with the applicable branch name.  As management made changes to the budget requirements I only had to amend the template sheet.