Consolidate files using VBA
Today we’ll be learning how to consolidate multiple excel files present in a folder save the output file using VBA. We’ll use FileSystemObject in VBA to achieve this.
Open the visual basic editor by pressing Alt+F11, Tools -> References -> Select “Microsoft Scripting Runtime” for the below code to function
Steps performed in the code
1. Create an empty workbook to store the output data
2. Setting up the file system objects and assigning the folder path where the input files are located
3. Loop through each file in the folder
4. Open the file, Set the first worksheet in the worksheet object
5. Store the last column address and last row in a variables
6. Copy the headers in case if the file counter is 1
7. Store the row in which the data need to be pasted in the output file
8. Copy the range of data from the input file and paste it in the output file
9. The tool will continue steps 4 to 8 for all the files available in the folder
10. Save the output file in the same folder path
11. Display a pop-up with a completion message
I tried to make the VBA code as dynamic as possible but you can modify the code as you want.
Keep visiting Analytics Tuts for more tutorials.
Thanks for reading! Comment your suggestions and queries.