Option Explicit
Dim fso As Scripting.FileSystemObject
Dim wkbQuelle As Workbook
Dim wksQuelle As Worksheet
Dim wksZiel As Worksheet
Dim m_sFolder As String
Const m_sPath As String = "ZU_DURCHSUCHENDER_PFAD"
Sub main()
Set wksZiel = ThisWorkbook.Worksheets(1)
Call ImportEachFile(m_sPath)
End Sub
Sub ImportEachFile(m_sFolder As String)
Dim oFolder As Scripting.Folder
Dim oFile As Scripting.File
Dim oSubFolder As Scripting.Folder
Set fso = New Scripting.FileSystemObject
For Each oSubFolder In fso.GetFolder(m_sFolder).SubFolders
Call ImportEachFile(oSubFolder.Name)
Next
For Each oFile In fso.GetFolder(m_sFolder).Files
Call FillOutMasterWorksheet(oFile.Path)
Next
End Sub
Function getCopyCurrentRegion(wks As Worksheet) As Variant
Dim arr()
Dim y As Long, x As Long
arr = wks.Range("A1").CurrentRegion
getCopyCurrentRegion = arr
End Function
Sub FillOutMasterWorksheet(sWorkbook As String)
Dim arr() As Variant
Set wkbQuelle = Workbooks.Open(sWorkbook, False)
With wkbQuelle
Set wksQuelle = .Worksheets(1)
arr = getCopyCurrentRegion(wksQuelle)
.Close True
End With
With wksZiel
.Cells(.Rows.Count, 1).End(xlUp).Offset(1, 0).Resize(UBound(arr, 1), UBound(arr, 2)).Value = arr
End With
Erase arr
Set wksQuelle = Nothing
Set wkbQuelle = Nothing
End Sub