The spreadsheet can be in the same workbook or in a different workbook file.Ideally, you should keep the consolidation process simple by opening all spreadsheets to be merged in the same workbook. Click the “Function” drop-down box, and then click “Sum” if you just want to replicate the original data in the master sheet.However the consolidate option applies an aggregate function to the data to summarize it.So consolidate doesn’t work if you want to keep the row details from each spreadsheet.Watch it on You Tube For more detail, here’s what i did to merge multiple excel files with MS Excel 2007. If you want to start new merge for multiple xls or xlsx files you have to clear the current worksheet, or create new file for merge.Sub simple Xls Merger() Dim book List As Workbook Dim merge Obj As Object, dir Obj As Object, files Obj As Object, every Obj As Object Application. I hope there’s also a way to merge spreadsheet similar to this but for Libre Office, since i also work on several ods files.You can also click another function to perform a calculation on the data as the sheets are merged. Click the “Browse” button and navigate to the workbook containing the first spreadsheet to merge -- this is necessary only if the spreadsheet is in a different workbook.
There isn’t a dedicated tool in Excel to append multiple datasheets or workbooks together.
Screen Updating = False Set merge Obj = Create Object("Scripting. Read also merge excel with simple merger tool for small files — merge without macros.