Multiple Workbooks to one.

[Claire Jackson]

Hi, I have multiple workbooks with weekly tabs where users add data (basically a spreadsheet that captures an employees name and what work he is doing that day for a full week per tab). Each Manager uses separate spreadsheets but I want to be able to print a list of each manager's entries on one single piece of paper, so I found the below on the web but although it brings the data in, it still puts it on separate sheets. Is there a way of combining the identically labelled tabs into just one sheet?

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

 

[awoohaw]

Have you considered skipping the VBA and using Power Query. If the workbooks are formatted consistently (structure and name) it is pretty straight forward.
Here is a great video on this from MyOnlineTrainingHub:

 

[dmt32]

Hi
Try this update

Sub mergeFiles()
    
    'Merges all files in a folder to a main file.
    'Define variables:
    Dim i               As Long
    Dim sourceWorkbook  As Workbook
    Dim rngData         As Range
    Dim ExcludeHeader   As Boolean
    Dim tempWorkSheet   As Worksheet, mainsheet As Worksheet
    
    Dim tblFiles        As ListObject
    Dim arrManagerFiles As Variant
    
    Set tblFiles = Worksheets("Manager Files").ListObjects(1)
    
    arrManagerFiles = tblFiles.DataBodyRange.Value
    
    '----------------------------------------------------------------------------------------------
    ' SETTINGS
    '----------------------------------------------------------------------------------------------
    'master sheet
    Set mainsheet = ThisWorkbook.Worksheets("Sheet1")
    
    'set True to exclude row 1 header row
    ExcludeHeader = True
    
    '----------------------------------------------------------------------------------------------
    
    On Error GoTo myerror
    
    Application.ScreenUpdating = False
    'Loop through all selected workbooks
    
    'clear previous manager records
    mainsheet.UsedRange.Offset(1).ClearContents
    
    For i = 1 To UBound(arrManagerFiles, 1)
        'Open each workbook
        Set sourceWorkbook = Workbooks.Open(arrManagerFiles(i, 1), 0, True)
        
        'Copy each worksheet to main worksheet
        For Each tempWorkSheet In sourceWorkbook.Worksheets
            Set rngData = tempWorkSheet.UsedRange
            If ExcludeHeader Then Set rngData = rngData.Offset(1).Resize(rngData.Rows.Count - 1)
            rngData.Copy mainsheet.Cells(mainsheet.Cells(mainsheet.Rows.Count, "A").End(xlUp).Row + 1, 1)
            Set rngData = Nothing
        Next tempWorkSheet
        
        'Close the source workbook
        sourceWorkbook.Close False
        Set sourceWorkbook = Nothing
    Next i
    
myerror:
    If Not sourceWorkbook Is Nothing Then sourceWorkbook.Close False
    Application.ScreenUpdating = True
    If Err <> 0 Then MsgBox (Error(Err)), 48, "Error"
End Sub

If not already done so, You will need to include the Filepath in the table

29-02-2024.xls
	A
1	Manager Files
2	C:\Users\clairejackson\Documents\manager\file1.xlsx
Manager Files

Dave