Consolidate Excel workbooks into a single workbook

They all live in the same folder and really just have one tab each.
They need to retain the tab names in the master workbook.
LVL 8
K BAsked:
Who is Participating?
 
Wayne Taylor (webtubbs)Commented:
If all you have in the folder are Excel files, you can use this which will copy all worksheets from each of the workbooks.

Sub ConsolidateWorkbooks()

    Application.ScreenUpdating = False

    Dim FileName As String, wb As Workbook, ws As Worksheet
    FileName = Dir(ThisWorkbook.Path & "\")
    Do While FileName <> ""
        If FileName <> ThisWorkbook.Name Then
            Set wb = Workbooks.Open(FileName)
            For Each ws In wb.Worksheets
                ws.Copy After:=ThisWorkbook.Worksheets(1)
            Next
            wb.Close False
        End If
        FileName = Dir
    Loop
    
    Application.ScreenUpdating = True
    
End Sub

Open in new window

0
 
Wayne Taylor (webtubbs)Commented:
The above routine should be run from the Master workbook, which should be saved in the same location as the workbooks to consolidate.
0
 
K BAuthor Commented:
Thank you very much!! I have one more out there fairly similar
http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/Q_28660327.html

also you helped me on http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/Q_28656054.html
and it errored out.   So close to finishing...it createds CSVs but hit a error.

Wayne as always thank you for your knowledge!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.