- Combine Data From Multiple Sheets Excel To Two
- Combine Data From Multiple Sheets In Excel
- Consolidate Data In Excel
A common problem in Excel is the need to combine data from different sheets to create a report based on the combined data. This post will demonstrate how we can append (combine) data from multiple sheets into a single table or directly into a Pivot Table.
- Mar 04, 2021 Start the Copy Sheets Wizard. On the Excel ribbon, go to the Ablebits tab, Merge group, click Copy Sheets, and choose one of the following options: Copy sheets in each workbook to one sheet and put the resulting sheets to one workbook. Merge the identically named sheets to one.
- Collect data from multiple sheets into one with Consolidate function 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.
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.
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
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)
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.
How to loop through sheets
how to get last row and column using vba
We can copy data from multiple worksheets into one by following the simple steps outlined below. In this tutorial, we will learn how to merge Excel sheets into one. We will also explore how to merge two Excel sheets, merge multiple sheets and use a VBA to combine sheets.
Figure 1 – How to merge excel documents
Combine Excel files into one using the Power Query tool
The Power Query tool sheets provide a quick and easy way to combine a lot of worksheets into one. When we have just a few sets of workbooks we want to merge, we can use these steps:
- We will open all the worksheets we wish to combine
Figure 2 – Sheet 1 for merge table from different sheets
Figure 3 – Sheet 2 for merging excel sheets into one
Figure 4 – Sheet 3 for merging excel sheets into one
- In the blank sheet, we will go to the Data Tab
- Next, we will click on Get External Data or Get Data (depending on Excel version)
Figure 5 – How to merge spreadsheets in excel
- In the drop-down list, we will go to FromOther Sources option. If we have Excel 2016, we will click on New Query and select Other Sources option.
Figure 6 – Merge two spreadsheets
- Next, we will click on Blank Query to open the Power Query editor
Figure 7 – How to merge two Excel Sheets
- In the Editor, we will enter the formula below in the formula bar:
Figure 8 – combine excel files
- We will hit the Enter key to show all table names
Figure 9 – merge excel documents
- To combine tables, we will click on the double pointed arrow in the content header cell
Figure 10 – Merge multiple excel files
- We will select all the columns we want to combine
- We will uncheck the Use original column name as prefix option
Figure 11 – How to merge two Excel sheets
- We will click OK
- Our worksheets will combine into a single table in the Power Query tab.
- After combining Excel sheets in the Power Query, we can load it in Excel
- We will click on the File table
- We will click on Close and Load To
Figure 12 – Merge Excel files using Power Query
- In the Import Data dialog box, we will select Table and New Worksheet option
Figure 13 – Combine excel files into one
- We will click OK
Figure 14 – How to combine multiple excel files into one worksheet.
Using a macro to combine multiple Excel files into one
When we have multiple Excel files, we can merge them in a swift manner using a VBA macro. We can easily use the macro displayed below.
- We will click on Alt + F11 and select Visual Basic Editor
- Next, we will right-click on This Workbook and select Insert, then Modules
- In the window that opens up, we will enter the code displayed below
Dim fnameList, fnameCurFile As Variant
Dim countFiles, countSheets As Integer
Dim wksCurSheet As Worksheet
Dim wbkCurBook, wbkSrcBook As Workbook
fnameList = Application.GetOpenFilename(FileFilter:='Microsoft Excel Workbooks (*.xls;*.xlsx;*.xlsm),*.xls;*.xlsx;*.xlsm', Title:='Choose Excel files to merge', MultiSelect:=True)
If (vbBoolean <> VarType(fnameList)) Then
If (UBound(fnameList) > 0) Then
countFiles = 0
countSheets = 0
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Set wbkCurBook = ActiveWorkbook
For Each fnameCurFile In fnameList
countFiles = countFiles + 1
Set wbkSrcBook = Workbooks.Open(Filename:=fnameCurFile)
Combine Data From Multiple Sheets Excel To Two
For Each wksCurSheet In wbkSrcBook.Sheets
countSheets = countSheets + 1
Combine Data From Multiple Sheets In Excel
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
MsgBox 'Processed ' & countFiles & ' files' & vbCrLf & 'Merged ' & countSheets & ' worksheets', Title:='Merge Excel files'
Consolidate Data In Excel
MsgBox 'No files selected', Title:='Merge Excel files'
- Now to run Macro, we will click on Alt +F8 to display Macro dialog
- Next, we will select MergeExcelFiles and tap, Run
Figure 15 – How to use a macro to combine excel files
Instant Connection to an Excel Expert
Most of the time, the problem you will need to solve will be more complex than a simple application of a formula or function. If you want to save hours of research and frustration, try our liveExcelchat service! Our Excel Experts are available 24/7 to answer any Excel question you may have. We guarantee a connection within 30 seconds and a customized solution within 20 minutes.