Need to Consolidate Multiple Excel files into one Workbook

[sarkark]

Hi there,

I have about 100 different excel files with multiple tabs. What I am trying to do is see if there is a quick way to consolidate all the worksheets in the multiple files into one new workbook. I can do it manually but moving the tabs from workbook to workbook however if there is a quick way to do this out there, your help is greatly appreciated.

thanks

 

[tigeravatar]

I'd suggest a macro:

Sub tgr()
    
    Dim strFldrPath As String
    Dim strCurrentFile As String
    Dim wbDest As Workbook
    Dim wb As Workbook
    Dim ws As Worksheet
    Dim arrWS() As String
    Dim i As Long
    
    With Application.FileDialog(msoFileDialogFolderPicker)
        .Title = "Select folder containing the Excel files"
        .Show
        On Error Resume Next: strFldrPath = .SelectedItems(1) & "\"
    End With
    
    If strFldrPath = vbNullString Then Exit Sub
    
    strCurrentFile = Dir(strFldrPath & "*.xls*")
    Set wbDest = ActiveWorkbook
    
    Application.ScreenUpdating = False
    
    While strCurrentFile <> vbNullString
        If strCurrentFile <> wbDest.Name Then
            Set wb = Workbooks.Open(strFldrPath & strCurrentFile)
            ReDim arrWS(1 To wb.Sheets.Count)
            i = 0
            For Each ws In wb.Sheets
                i = i + 1
                arrWS(i) = ws.Name
            Next ws
            wb.Sheets(arrWS).Copy after:=wbDest.Sheets(wbDest.Sheets.Count)
            wb.Close False
        End If
        strCurrentFile = Dir
    Wend
    
    Application.ScreenUpdating = True
    
End Sub

 

[dbennett]

I have the same problem, except I only want to copy worksheets 3 - 6 to the new workbook. Can anyone help me with what I need to change in the code to achieve this?

Thanks