How to collect data from multiple sheets to a master sheet in Excel?
In some time, you may need to collect data from multiple sheets into one master sheet for consolidating or doing other operations as below screenshot shown, but to copy and paste the contents one by one is troublesome, is there any tricks can quickly solve it in Excel?
Collect data from multiple sheets into one with Consolidate function
Collect data from multiple sheets into one with VBA code
Collect data from multiple sheets into one with powerful Combine function
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.
1. In a new sheet of the workbook which you want to collect data from sheets, click Data > Consolidate.
2. In the Consolidate dialog, do as these:
(1 Select one operation you want to do after combine the data in Function drop down list;
(2 Click 
to select the range of each sheet you want to collect;
(3 Click Add button to add the data range into the All references list box;
(4 Check the labels you use after combine data, and check Create links to source data option if you want to link the data in combine sheet with source data.
3. Click OK. Now the data have been collect and sum in one sheet.
Easily Combine multiple sheets/Workbook into one Single sheet or Workbook
|
| To combinne multiples sheets or workbooks into one sheet or workbook may be edious in Excel, but with the Combine function in Kutools for Excel, you can combine merge dozens of sheets/workbooks into one sheet or workbook, also, you can consolidate the sheets into one by several clicks only. Click for full-featured 30 days free trial! |
 |
| Kutools for Excel: with more than 300 handy Excel add-ins, free to try with no limitation in 30 days. |
Collect data from multiple sheets into one with VBA code
Here is a VBA code can collect data from all sheets in a new sheet in the current workbook.
1. Press Alt + F11 key to enable the Microsoft Visual Basic for Applications window.
2. Click Insert > Module, and copy and paste below code to the Module script.
VBA: Collect all data from sheets into one.
Sub Combine()
'UpdatebyExtendoffice20180205
Dim I As Long
Dim xRg As Range
On Error Resume Next
Worksheets.Add Sheets(1)
ActiveSheet.Name = "Combined"
For I = 2 To Sheets.Count
Set xRg = Sheets(1).UsedRange
If I > 2 Then
Set xRg = Sheets(1).Cells(xRg.Rows.Count + 1, 1)
End If
Sheets(I).Activate
ActiveSheet.UsedRange.Copy xRg
Next
End Sub
3. Press F5 key to run the code, all data of the whole workbook has been collected in the new sheet Combined.
Collect data from multiple sheets into one with powerful Combine function
If you usually need to combine sheets into one, I introduce one powerful combine function for you, it’s Kutools for Excel’s Combine utility which have below four main functions:
Combine multiple worksheets from workbook into one worksheet
Combine all same name worksheets into one worksheet
Combine multiple worksheets from workbooks into one workbook
Consolidate and calculate values across multiple workbooks into one worksheet
| Kutools for Excel, with more than 300 handy functions, makes your jobs more easier. | ||
After free installing Kutools for Excel, please do as below:
If you just want to combine sheets into one sheet without any other operations, you can do as these:
1. Enable Excel, click Kutools Plus > Combine, there is one or two dialogs pop out to remind you some notices, just click OK and Yes to continue. See screenshot:
2. Click OK to go to the Combine window, check Combine multiple worksheets from workbook into worksheet option.
3. Click Next, then click Add > File / Folder to add workbooks you will use to combine into the Workbook list. Then go to Worksheet list to check the sheets you want to combine form each workbooks.
4. Click Next to go to the last step, you can specify some setting about the combine result as you need. If you do not need this step.
5. Click Finish and select one folder to place the new workbook which combine data from sheets.
6. Click Save. Now all data has been collected from sheets across workbook into one master sheet.
Combine all sheets from workbooks into one worksheet
If you want to consolidate worksheets across workbooks into one, do as these:
1. In the Combine window, check Consolidate and calculate values across multiple worksheets into one worksheet option.
2. Click Next, and add files into Workbook list, then check the sheets you use to combine and calculate.
3. Click Next, then select one calculation from Function drop down list, and check other options as you need.
4. Click Finish to save the new workbook into a folder.
Now the sheets across workbooks have been consolidated and calculated into one sheet.
Comsolidate worksheets from workbooks into one sheet
| Kutools for Excel: 300 functions you must have in Excel, 30-day free trial from here. |
Best Office Productivity Tools
Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time. Click Here to Get The Feature You Need The Most...
Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier
- Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
- Open and create multiple documents in new tabs of the same window, rather than in new windows.
- Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
