Option Explicit
Sub GetData()
Dim wbIn As Workbook, wbOut As Workbook
Dim rIn As Range, rOut As Range
Dim wsIn As Worksheet, wsOut As Worksheet
Dim sPath As String, strExtension As String
Dim diaFolder As FileDialog
Dim lCount As Long
Set wbOut = ThisWorkbook
' Open the file dialog to get the folder for the files
Set diaFolder = Application.FileDialog(<wbr>msoFileDialogFolderPicker)
diaFolder.AllowMultiSelect = False
diaFolder.Show
sPath = diaFolder.SelectedItems(1)
If Right(sPath, 1) <> "\" Then
sPath = sPath & "\"
End If
'Change extension
ChDir sPath
'get the first file name
strExtension = Dir("*.xls*")
'Speed up processing by disabling screen updating and events
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Application.EnableEvents = False
'Loop through all the books
Do While strExtension <> ""
Set wbIn = Workbooks.Open(sPath & strExtension)
'loop through all the sheets in the opened book
For Each wsIn In wbIn.Sheets
'check if name is in our list
Select Case wsIn.Name
Case "CBOE - Labor", "Labor", "Labor Ledger Costs"
'yes, now select the same named sheet in our summary WB
Set wsOut = wbOut.Sheets(wsIn.Name)
'set output range to last row
Set rOut = wsOut.Cells(wsOut.Rows.Count, 1).End(xlUp).Offset(1, 0)
'now copy the values accross
With wsIn.Range("A1").CurrentRegion
rOut.Resize(.Rows.Count, Columns.Count).Value = .Value
End With
End Select
Next wsIn
'close WB
wbIn.Close savechanges:=False
'Get next WB name
strExtension = Dir
Loop
Application.ScreenUpdating = True
Application.DisplayAlerts = True
Application.EnableEvents = True
'Cleanup
Set wbIn = Nothing
Set wbOut = Nothing
Set rIn = Nothing
Set rOut = Nothing
Set wsIn = Nothing
Set wsOut = Nothing
Set diaFolder = Nothing
End Sub
