this may give you a start. i am not sure.
Sub vbax_57678_cons_multi_ws_wb_mac()
'uses GetFilesOnMacWithOrWithoutSubfolders function from:
'https://msdn.microsoft.com/en-us/library/office/jj613789(v=office.14).aspx
Dim MyFiles As String
Dim Mybook As Workbook
Dim wSource As Worksheet, wTarget As Worksheet
Dim MySplit
Dim FileInMyFiles As Long, CalcMode As Long
With Application
CalcMode = .Calculation
.Calculation = xlCalculationManual
.ScreenUpdating = False
.EnableEvents = False
End With
Set wTarget = ThisWorkbook.Worksheets("GraphData")
MyFiles = ""
Call GetFilesOnMacWithOrWithoutSubfolders(Level:=1, ExtChoice:=0, FileFilterOption:=0, FileNameFilterStr:="")
'Level : 1= Only the files in the folder you select, 2 to ? levels of subfolders
'ExtChoice : 0=(xls|xlsx|xlsm|xlsb), 1=xls , 2=xlsx, 3=xlsm, 4=xlsb, 5=csv, 6=txt, 7=all files, 8=(xlsx|xlsm|xlsb), 9=(csv|txt)
'FileFilterOption : 0=No Filter, 1=Begins, 2=Ends, 3=Contains
'FileNameFilterStr : Search string used when FileFilterOption = 1, 2 or 3
If MyFiles <> "" Then
MySplit = Split(MyFiles, Chr(10))
For FileInMyFiles = LBound(MySplit) To UBound(MySplit) - 1
Set Mybook = Workbooks.Open(MySplit(FileInMyFiles))
With Mybook
For Each wSource In .Worksheets
If wSource.Name <> "Definition" Then
Set PasteRng = wTarget.Cells(2, Columns.Count).End(xlToLeft).Offset(, 1)
PasteRng.Value = MySplit(FileInMyFiles) & "_" & wSource.Name
wSource.Range("C3:C10").Copy
PasteRng.Offset(1).PasteSpecial
End If
Next wSource
.Close False
End With
Next FileInMyFiles
End If
With Application
.ScreenUpdating = True
.EnableEvents = True
.Calculation = CalcMode
End With
End Sub