Working as a DF Analyst, a lot of my time is spent in spreadsheets searching and identifying various artefacts of note that are relevant to the investigation. I am often Googling for the same VB script to combine multiple independent spreadsheets into one workbook.
It made sense to post it here so I can always grab it when I need it. Credit goes to Professor Excel for the VB script (https://professor-excel.com/merge-excel-files-combine-workbooks-one-file/)
Sub mergeFiles() 'Merges all files in a folder to a main file. 'Define variables: Dim numberOfFilesChosen, i As Integer Dim tempFileDialog As fileDialog Dim mainWorkbook, sourceWorkbook As Workbook Dim tempWorkSheet As Worksheet Set mainWorkbook = Application.ActiveWorkbook Set tempFileDialog = Application.fileDialog(msoFileDialogFilePicker) 'Allow the user to select multiple workbooks tempFileDialog.AllowMultiSelect = True numberOfFilesChosen = tempFileDialog.Show 'Loop through all selected workbooks For i = 1 To tempFileDialog.SelectedItems.Count 'Open each workbook Workbooks.Open tempFileDialog.SelectedItems(i) Set sourceWorkbook = ActiveWorkbook 'Copy each worksheet to the end of the main workbook For Each tempWorkSheet In sourceWorkbook.Worksheets tempWorkSheet.Copy after:=mainWorkbook.Sheets(mainWorkbook.Worksheets.Count) Next tempWorkSheet 'Close the source workbook sourceWorkbook.Close Next i End Sub