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