Consolidating excel files
Follow the steps below for the type of file you're trying to merge.
He had a lot of files in a folder and he wanted to get all the worksheets from all the workbooks into one single workbook.Pivot Table - The first example works on multiple files, which must have the data in identical structures, and you can read the instructions on my blog.To see Kirill's pivot table code, you can download the Pivot Workbooks example.While this can be done manually, it would be time-consuming and error-prone.However, a simple VBA code can do this in a few seconds.You can read more about MS Queries here: Instead of manually setting up a union query, you can use the code in a sample file from Excel MVPs, Kirill Lapin (KL), with amendments by Hector Miguel Orozco Diaz.
Before you use the sample code, replace the sample sheet names with the sheet names in your workbook.
The zipped folder that contains the file, and the five sample data files.
Unzip the folder, and keep all the files in the same folder.
To do this, open the first file in MS Word, and follow the steps for your version of Word.
The steps are different because of the changes between the file menu and the Office ribbon.
You can also use it to import multiple files at once into your workbook. It can therefore be time consuming and frustrating if you for example need to import or merge 200 Excel or CSV files.