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
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!
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.
Dim I As Long
Dim xRg As Range
On Error Resume Next
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)
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
Select Duplicate or Unique Rows; Select Blank Rows (all cells are empty); Super Find and Fuzzy Find in Many Workbooks; Random Select...
Exact Copy Multiple Cells without changing formula reference; Auto Create References to Multiple Sheets; Insert Bullets, Check Boxes and more...
Extract Text, Add Text, Remove by Position, Remove Space; Create and Print Paging Subtotals; Convert Between Cells Content and Comments...
Super Filter (save and apply filter schemes to other sheets); Advanced Sort by month/week/day, frequency and more; Special Filter by bold, italic...
Combine Workbooks and WorkSheets; Merge Tables based on key columns; Split Data into Multiple Sheets; Batch Convert xls, xlsx and PDF...
More than 300 powerful features. Supports Office/Excel
2007-2019 and 365. Supports all languages. Easy deploying in your enterprise or organization. Full features
30-day free trial. 60-day money back guarantee.
Hi, I'm trying to accomplish the following: Get data from multiple tabs in a specific cell range (B3-E169). Data would be compiled in columns...i.e., column B copies into column B on new spreadsheet, column C copies into column C on new spreadsheet, etc. If there are more than two blank columns in B, stop & move onto next worksheet and get same data. The result that I'm trying to get is a material list from different categories (hence the different tabs).
This is how I think it should work: Lookup WS1 column B3-B169. If two blank columns, Lookup WS2 column B3-B169, If two blank columns, Lookup WS3 column B3-B169, etc.
WS2 should skip a space/column & come in underneath WS1. WS3 should skip a space/column & come in underneath WS2,.etc.
De que forma se puede obtener solo el valor de las hojas y llevarla a la hoja que se va a resumir todo, por ejemplo, en caso de que una hoja de las que se van a combinar exista una celda calculada o que dependa de otra hoja, por eso pregunto si se puede llevar solo los valores.