This is also an ideal time to set up your template with Excel tables.
There are two ways to consolidate data: by Category or by Position.
Summarize data from multiple worksheets/workbooks into one worksheet with Consolidate function Summarize data from multiple worksheets/workbooks into one worksheet with Kutools for Excel This Consolidate feature combines values and do some calculations from multiple worksheets or workbooks into one worksheet.
In the following example, I want to summarize the total score of four terms for each student.
Select all of the worksheet names of a workbook in tab bar.
You can select multiple with holding down Ctrl key or Shift key.
Just close the source wookbook and do it again with the next one, until you've collected all the worksheets you care about into one large wookbook. Would you like to answer one of these unanswered questions instead?
To summarize and report results from separate worksheets, you can consolidate data from each into a master worksheet.
Tip: If you frequently consolidate data, it might help to base your worksheets on a worksheet template that uses a consistent layout.
To learn more about Templates, see: Create a template.
You create a "table" for each spreadsheet to be merged You create a "query" which pulls the desired columns to one sheet Please see the Consolidate Worksheets Wizard add-in for Excel The add-in has several merge modes, one of them does exactly what you need.
Please see this link for a detailed description of the mode (how to combine sheets with the same name to one) The add-in is a shareware, but it has a 15-day fully-funtional trial version (download button at the top of the page), so you can merge thousands of your workbooks for free :) The selected worksheets will be moved or copied from the original workbook into your "Master" workbook. Because it has attracted low-quality or spam answers that had to be removed, posting an answer now requires 10 reputation on this site (the association bonus does not count).
Using the Move or Copy command will help you export or copy one or several worksheets to a new workbook quickly.1.