Need to Consolidate Multiple Excel files into one Workbook

sarkark

New Member
Joined
Jan 7, 2011
Messages
3
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
 

Some videos you may like

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.

tigeravatar

Well-known Member
Joined
Aug 12, 2011
Messages
760
I'd suggest a macro:
Code:
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

New Member
Joined
Jun 3, 2011
Messages
7
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 :)
 

Watch MrExcel Video

Forum statistics

Threads
1,122,461
Messages
5,596,276
Members
414,051
Latest member
tabecker

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top