Overall, there are four ways to merge Excel worksheets into one without copying and pasting:
Combine sheets with Ultimate Suite
Power Query for Excel
Merge two Excel sheets into one by the key column(s)
How to combine Excel spreadsheets with Ultimate Suite
The built-in Excel Consolidate feature can summarize data from different sheets, but it cannot combine sheets by copying their data. For this, you can use one of the merge & combine tools included with our Ultimate Suite for Excel.
Combine multiple worksheets into one with Copy Sheets
Supposing you have a few spreadsheets that contain information about different products, and now you need to merge these sheets into one summary worksheet, like this: