Tip: Other languages are Google-Translated. You can visit the English version of this link.
Log in
x
or
x
x
Register
x

or
0
0
0
s2smodern

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 functiongood idea3

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 60 days free trial!
combine sheets
 
Kutools for Excel: with more than 200 handy Excel add-ins, free to try with no limitation in 60 days.

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.
doc collect sheets into one 1

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 doc select button 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.
doc collect sheets into one 2

3. Click OK. Now the data have been collect and sum in one sheet.
doc collect sheets into one 3


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
doc collect sheets into one 4

3. Press F5 key to run the code, all data of the whole workbook has been collected in the new sheet Combined.
doc collect sheets into one 5


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
doc collect sheets into one 6

Kutools for Excel, with more than 120 handy functions, makes your jobs easier. 

After installing Kutools for Excel, please do as below:(Free Download Kutools for Excel Now!)

If you just want to combine sheets into one sheet without any other operations, you can do as these:

1. Enable Excel, click Enterprise > Combine, there is one dialog pops out to remind you some notices. See screenshot:
doc collect sheets into one 7
doc collect sheets into one 8

2. Click OK to go to the Combine window, check Combine multiple worksheets from workbook into worksheet option.
doc collect sheets into one 91

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.
doc collect sheets into one 10
doc collect sheets into one 11

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.
doc collect sheets into one 12

5. Click Finish and select one folder to place the new workbook which combine data from sheets.
doc collect sheets into one 13

6. Click Save. Now all data has been collected from sheets across workbook into one master sheet.
doc collect sheets into one 14

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.
doc collect sheets into one 15

2. Click Next, and add files into Workbook list, then check the sheets you use to combine and calculate.
doc collect sheets into one 16

3. Click Next, then select one calculation from Function drop down list, and check other options as you need.
doc collect sheets into one 17

4. Click Finish to save the new workbook into a folder.

Now the sheets across workbooks have been consolidated and calculated into one sheet.
doc collect sheets into one 18


Recommended Productivity Tools

Office Tab

gold star1 Bring handy tabs to Excel and other Office software, just like Chrome, Firefox and new Internet Explorer.

Kutools for Excel

gold star1 Amazing! Increase your productivity in 5 minutes. Don't need any special skills, save two hours every day!

gold star1 200 New Features for Excel, Make Excel Much Easy and Powerful:

  • Merge Cell/Rows/Columns without Losing Data.
  • Combine and Consolidate Multiple Sheets and Workbooks.
  • Compare Ranges, Copy Multiple Ranges, Convert Text to Date, Unit and Currency Conversion.
  • Count by Colors, Paging Subtotals, Advanced Sort and Super Filter,
  • More Select/Insert/Delete/Text/Format/Link/Comment/Workbooks/Worksheets Tools...

Screen shot of Kutools for Excel

btn read more      btn download     btn purchase

Say something here...
symbols left.
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.

Be the first to comment.