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.
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)
            wb.Close False
        End If
        FileName = Dir
    Application.ScreenUpdating = True
End Sub

Open in new window

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.
K BAuthor Commented:
Thank you very much!! I have one more out there fairly similar

also you helped me on
and it errored out.   So close to createds CSVs but hit a error.

Wayne as always thank you for your knowledge!
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.