Combing workheets into one workboolk

Joanne_T_1323 

Place all the workbooks that you want to combine in the same folder, without other workbooks.

I'll assume that each workbook has one worksheet (or if it has multiple sheets, that you want the first one).

Run this macro. With 150 workbooks, it'll take a while.

Sub CombineWorkbooks()
    Dim strFolder As String
    Dim strFile As String
    Dim wbkSource As Workbook
    Dim wbkTarget As Workbook
    With Application.FileDialog(4) ' msoFileDialogFolderPicker
        If .Show Then
            strFolder = .SelectedItems(1)
        Else
            Beep
            Exit Sub
        End If
    End With
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    Set wbkTarget = Workbooks.Add(Template:=xlWBATWorksheet)
    If Right(strFolder, 1) <> "\" Then
        strFolder = strFolder & "\"
    End If
    strFile = Dir(strFolder & "*.xls*")
    Do While strFile <> ""
        Set wbkSource = Workbooks.Open(Filename:=strFolder & strFile)
        wbkSource.Worksheets(1).Copy After:=wbkTarget.Worksheets(wbkTarget.Worksheets.Count)
        wbkSource.Close SaveChanges:=False
        strFile = Dir
    Loop
    wbkTarget.Worksheets(1).Delete
    Application.DisplayAlerts = True
    Application.ScreenUpdating = True
End Sub