I have a folder with 25 files, one sheet each, all headers are the same. I want to combine all these .xlsx files into one workbook as one tab each using the filename from each file (not the tab names).
The code below works to combine all the files, but I need to insert code which will rename the tabs based on filenames. This is important for creating unions out of Tableau.
'Merges all files in a folder to a main file.
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
Set sourceWorkbook = ActiveWorkbook
'Copy each worksheet to the end of the main workbook
For Each tempWorkSheet In sourceWorkbook.Worksheets
'Close the source workbook