Automated Data Collator in Excel

Collating data from several Excel files to one file is really hectic job and it takes a lot of time and manual efforts. Also, there is a high possibility of errors in copying and pasting data. We might miss any of the file or paste the data under different column headers.

Excel always plays an important role in automating the task with Macro or BA code. In this article, we will learn how to create a fully automated data collator tool in Excel and VBA. Please follow the below steps.

Step 1

Create a New Excel File and save it with the name ‘Automated Data Collator’. Make sure the extension should be ‘.xlsm’ otherwise file will not retain the VBA codes.

Step 2

Add two different sheets in the ‘Automated Data Collator’ file. Rename the ‘Sheet1’ to ‘Home’ and ‘Sheet2’ to ‘Collated Data’.

Step 3

Create the label and add a command button (rounded rectangle) from shape with ‘Collate’ caption. Please see the below image.

Step 4

Add the required column headers in ‘Collated Data’ sheet in row 1. Please see the below image and use the same headers and formatting.

Step 5

Let’s move to the Visual Basic Application window. To jump to VBA window, click on Developer Tab then click on VBA in code group. Alternatively, you can press shortcut key ALT + F11.

In this tool, we will utilize FileSystemObject(FSO) and it’s methods. So, let’s add the reference of FSO. To do that, click on Tools Menu and then click on ‘Reference’ and select ‘Microsoft Scripting Runtime’ from the available references. Please see the below image.

Step 6

To write the codes, let’s insert a new Module in our project. To add a module, click on Insert menu then click on ‘Module’.

Step 7

Let’s add the below VBA codes in Module1 code window.

Now, we have done with coding. Let’s move to Excel window and assign the macro on ‘Collate’ button available on ‘Home’ sheet.

Please watch our Step-By-Step YouTube tutorial.

Please click on below button to download the Excel File (Data Collator) used in our tutorial.

Click to download


Please enter your comment!
Please enter your name here