By Mary Pat Campbell
Many years ago, I had “ownership” of a monster spreadsheet that had been used for preparing exhibits for an insurer’s Annual Statement. This spreadsheet had grown over the years, with each additional owner adding their own touch to try to make sure the results were correct. However, no one ever had enough time to really do a thorough clean up job.
Somewhere along the way, pivot tables became the heart of these spreadsheets. Pivot tables were used to summarize the results from hundreds of rows, with rows being added and updated throughout the process. You need to know only one fact about pivot tables: while they are a quick and flexible tool to summarize, aggregate, and otherwise slice-and-dice data, they do not automatically update or “refresh” when the underlying data is changed.
Join Mary Pat on December 11th for her latest webinar: Excel VBA to Document and Create Reports
At least ten pivot tables lived in this particular file, spread out over even-more-numerous sheets within the workbook. Invariably, as data was updated and appended, the pivot tables being used for aggregation would get out-of-sync with that data. There were counter-checks to results (primarily: do the reserves add up to the same number for every different exhibit?), but time was wasted from the various counterchecks and reminders to manually update things.
I knew there had to be a better way.
I wrote VBA (Visual Basic for Applications) code to refresh all the pivot tables in the workbook:
Now, Microsoft did update Excel such that one can do as the above with a single button click (needless to say, I wrote the above code before that feature was added). However, the “Refresh All” button really does refresh everything – links to external files and data sources, in addition to refreshing pivot tables. However, it does simplify the code:
However, if one wanted to refresh only the pivot tables, my first set of code is what you would want to use.
One need not know a great deal of VBA (nor of computer science) in order to get usefulness out of VBA. Automating small steps that one has done manually can not only save yourself a lot of time (and boredom), but it can ensure repetitive tasks are done correctly.
One of the most notorious sources of spreadsheet error is the transfer of results from one spreadsheet to another location (the infamous “copy/paste” errors). When this sort of transfer is done manually, this can lead to items being pasted to the wrong location. Being off by one row or one column can be the source of errors that then propagate throughout one’s work. In the European Spreadsheet Risk Interest Group collection of horror stories, manual processes have led to large losses… and that’s just the ones that have made the news. Most of these manual processes could be replaced with an automated one, precluding these errors.
Automate the boring and nit-picky things away with VBA – you’ll be glad you did!
In my on-demand webinar: Automating with VBA in Excel, I covered how to use VBA to do some automation, including how to take results from the built-in macro recorder to make it repeatable for useful code.
in my upcoming webinar: Excel VBA to Document and Create Reports, December 11, 2019, I will look at ways you can use VBA to automatically generate documentation and reports from your spreadsheets.