Jan 06, 2019 The Best Possible Way for Combining Excel Files by Merging data into ONE Workbook - POWER QUERY Power Query is the best way to merge or combine data from multiple Excel files in a single file. You need to store all the files in a single folder and then use that folder to load data from those files into the power query editor. Consolidate data in multiple worksheets Excel for Microsoft 365 Excel 2019 Excel 2016 Excel 2013 Excel 2010 Excel 2007 To summarize and report results from separate worksheets, you can consolidate data from each sheet into a master worksheet. The sheets can be in the same workbook as the master worksheet, or in other workbooks. Click the worksheet that contains the data you want to consolidate, select the data, and then click the Expand Dialog button on the right to return to the Consolidate dialog. If a worksheet containing data that you need to consolidate is in another workbook, click Browse to locate that workbook. If you want to collect data from multiple sheets into one sheet in the same workbook, you can apply the Consolidate function in Excel. In a new sheet of the workbook which you want to collect data from sheets, click Data Consolidate.
Sometimes we want to merge multiple sheets into one sheet so that we can easily analyse the data and turn it into some useful information. This articles will tell you how to merge multiple worksheets into one worksheet using VBA.
Example:
Here I have fetched some data from server that returns data into different worksheets. I have added one more sheet and named it as “Master”. Other sheet names doesn’t matter.
Now run this macro.
Example:
Here I have fetched some data from server that returns data into different worksheets. I have added one more sheet and named it as “Master”. Other sheet names doesn’t matter.
Now run this macro.
How to merge sheets using this VBA Macro?
- Insert a new sheet and name it “Master” in the workbook. Rename it later if you want.
- Insert a module in VBA editor and copy above VBA code.
- Run the macro.
- You will be asked to select headings. Select the heading and hit OK.
And it is done. All the sheets are merged in master.
How it works?
I assume that you know the basics of object and variable creation in VBA. in the first part we have created object and variables that we will need in our operations.
How it works?
I assume that you know the basics of object and variable creation in VBA. in the first part we have created object and variables that we will need in our operations.
Well most of the things I have explained using comments in vba code. Let’s look at the main part of this vba code.
In earlier articles we learned how to loop through sheets and how to get last row and column using vba.
Here we are looping through each sheet in main workbook using for loop.
For Each ws In wb.Worksheets
For Each ws In wb.Worksheets
Then we exclude “master” sheet from looping, since we will be consolidating our data in that sheet.
Then we get last row and last column number.
Now next line is very important. We have done multiple operations into one line.
Range(Cells(startRow, startCol), Cells(lastRow, lastCol)).Copy _
mtr.Range('A' & mtr.Cells(Rows.Count, 1).End(xlUp).Row + 1)
Range(Cells(startRow, startCol), Cells(lastRow, lastCol)).Copy _
mtr.Range('A' & mtr.Cells(Rows.Count, 1).End(xlUp).Row + 1)
First we form a range using startRow, startCol and lastRow and lastCol.
This loops runs for all the sheets and copies each sheets data into master sheet.
Finally, in the end of the macro we activate the mastersheet to see the output.
So yeah guys, this is how you can merge every sheet in a workbook. Let me know if you have any query regarding this VBA code or any excel topic in the comments section below.
Download file:
Download file:
Related Articles:
How to loop through sheets
how to get last row and column using vba
Popular Articles:
How to combine multiple workbooks into one worksheet? Our Excel data processing tools series continues! It’s a very important task if you consolidate raw data.
A short time ago we got acquainted with the Excel worksheet splitter that gave us a big help cutting up and grouping data tables.
It is possible that we don’t have to cup up the data table but we need to do just the opposite.
Combine Two Workbooks Excel
As a business analyst and advanced Excel user I often have the task of merging more (usually a 100+ piece) Excel worksheets or workbooks into one single table, possibly in a very short time.
One of the favorite habits of managers is to try to over-persuade their employees to better performance by impossible deadlines, but that’s how the cookie crumbles.
Combining Data From Multiple Spreadsheets
Combine Multiple Workbooks – Folder location
If we don’t automate these procedures we may struggle for ever with this task. This Excel know-how can come in handy for you, too, at any time!
That’s why today’s lesson will be about combining multiple workbooks in such way that the end result will be one single Excel file.
In this case the Excel supported automated data processing add-in will help the quick execution of our processes. As usual the add-in can be reached from the ribbon.
Our first task is to determine the location of those Excel files that we want to combine. By clicking the Browse command button we can start browsing the folders containing the files.
Next to the button we have placed a checkbox.
If you have it checked than at the combination of the files not only the folder but its sub-folders will be taken into account by the program at the analyzation of the workbooks to be combined.
For example frequent practice to group the marketing data by years and the monthly data will be placed into sub-folders.
Join Multiple Workbooks – File Types
[adinserter block=”2″]
First of all let’s talk about what kind of files are those that the combination can be applied to. Currently the .xls, .xlsx, .xlsm and .xlsb files are supported by the program. In the next version we would like to introduce the .xml and .csv types also.
![Excel Combine Data From Multiple Workbooks Excel Combine Data From Multiple Workbooks](/uploads/1/3/7/7/137705381/674056467.jpg)
After choosing the file types we can decide that we interlink all the files or only just some of them. We can choose from three checkboxes.
The first option is to choose the files manually. It is best to choose this option when we want to combine only a few Excel files.
As the second option we can choose that the program processes all the files in the folder. There is not any limitation, the add-in reads in all of the workbooks and combines the data can be found therein.
The third option enables special settings. If we have to choose from thousands of files that’s names contains the word *sales* than write in this word. So only the sales data will be in one table.
Of course this is only an example; we can search by virtually any word or word-fragment.
Combine Multiple Workbooks – Options
It seems we are making great progress. Let’s go to the next step! In the first two steps we have determined the file types and the output folder.
The question is do we have to process all the worksheet from the file? Because one workbook usually contains several worksheets, the question is valid!
We can choose the most appropriate one from the ones on the picture. We can include all of the worksheets into the interlinking, but we can give a specific name also. In this case we should choose based on the type of the current task.
Merge Multiple Workbooks – Additional Settings
Finally determine that in what range the data on each worksheet located at. If we exactly know their place than in the fixed range field we can set the range, for example A1 : D10.
In this case not the whole content of the worksheet will be placed into the interlinked file but only the determined range of it.
If there isn’t such limitation, than choose the second checkbox. As an effect of this the VBA code will not takes the worksheet ranges into consideration but automatically works with all the information.
If we would like to keep the data in its original structure than leave the “Paste as Values” checkbox blank. If you would also like to keep the format (colors, etc.) than mark this checkbox.
Combine Multiple Workbooks – DataXL Add-in
If you experience that the graphic user interface (GUI) is often changing, that is no accident. We have already determined the final goal which is to build a complex Excel data processing add-in.
Combine Excel Files Into One
We apply developments continually. That’s why there may be smaller changes on the GUI (graphical user interface). But we guarantee that these changes only affect the user interface, the just now introduced functions will only expand.
Finally let’s say a few words about compatibility! We have tested the Combine Multiple Workbooks add-in in Excel 2007 – Excel 2016. Download free excel add-ins and tools!
Additional resources