Almost any recurring or repetitive task that’s performed in an Office application can be programmed to complete within a fraction of the time it takes to manually complete it.
All Microsoft Office applications come packed full of features that can be used to streamline your current processes or completely automate them with a little clever coding.
Consolidating multiple files can be a time consuming task, that involves finding each file that contains relevant information, selecting the correct tabs to copy across to the master workbook, closing the file (making sure any changes aren’t saved), then repeating the process with the next file. And this is just a simple copy of worksheets. More often than not, there’s more to the task… The worksheets will need to be re-named to a string of text within cell B12, the master workbook will need to be updated with formulas to collect information from the new worksheets, there’s dozens of files to repeat the process with, the source files will need to have data added to them and the cell colour changed, etc, etc. The tasks required by any given business are as unique as their operations.
We design functions to consolidate multiple files into one spreadsheet with the click of a button.
The consolidation of multiple files is a task that’s regularly required to be automated as a small part of a bigger project and we’ve been involved in designing a number of automation projects.
We’ve programmed the attached sample file with the macro code to import all worksheets within all files within a selected folder into one master file. While this solution might work well enough for a purpose, it’s unlikely that a ‘cookie cutter’ solution will be the perfect fit for anyone. Automating processes for any business requires a unique approach, considering the tasks required, the properties of the source data and the application of the end product. We welcome your call to discuss automation of your current processes with you – we think you’ll be pleasantly surprised by the cost and how quickly you’ll recoup your investment.
Download a simple consolidation macro
The attached downloadable Excel file features a macro to import all worksheets from all spreadsheet files in a folder into one Excel file.