ich habe folgenden Code um Daten aus verschiedenen Dateien aus einem Ordner zu lesen.
Der Code klappt soweit einwandfrei.
Nun möchte ich allerdings die Tabelle in der Zieldatei in einem dynamische Tabelle umwandeln.
Hintergrund: Ich möchte Diagramme erstellen, die sich automatisch um die neuen Zeilen erweitern.
Wenn ich die Tabelle in der Zieldatei allerdings in eine dynamische Tabelle umwandle, dann werden die Daten aus den Dateien nicht mehr richtig in die Tabelle eingelesen.
(alles ist durcheinander)
Kann mir jemand sagen woran das liegt?
Private Sub DatenHolen_Click()
Dim wb As Workbook, ws As Worksheet
Dim fso As Object, fldr As Object, wbFile As Object
Dim y As Long, z As Long
ThisWorkbook.Sheets(1).Range("A5:AD" & Rows.Count).ClearContents
Application.ScreenUpdating = False
Set fso = CreateObject("Scripting.FileSystemObject")
Set fldr = fso.GetFolder(ThisWorkbook.Path & "\")
y = ThisWorkbook.Sheets(1).Cells(Rows.Count, 1).End(xlUp).Row + 1
For Each wbFile In fldr.Files
If fso.GetExtensionName(wbFile.Name) = "xlsx" Then
Set wb = Workbooks.Open(wbFile.Path)
For Each ws In wb.Sheets
ThisWorkbook.Sheets(1).Cells(y, 1) = Format(ws.Cells(1, 2), "dd.mm.yyyy")
ThisWorkbook.Sheets(1).Cells(y, 2) = ws.Cells(8, 2)
ThisWorkbook.Sheets(1).Cells(y, 3) = ws.Cells(10, 2)
ThisWorkbook.Sheets(1).Cells(y, 4) = ws.Cells(12, 2)
ThisWorkbook.Sheets(1).Cells(y, 5) = ws.Cells(17, 2)
ThisWorkbook.Sheets(1).Cells(y, 6) = ws.Cells(19, 2)
ThisWorkbook.Sheets(1).Cells(y, 7) = ws.Cells(23, 2)
ThisWorkbook.Sheets(1).Cells(y, 8) = ws.Cells(25, 2)
ThisWorkbook.Sheets(1).Cells(y, 9) = ws.Cells(32, 2)
ThisWorkbook.Sheets(1).Cells(y, 10) = ws.Cells(34, 2)
ThisWorkbook.Sheets(1).Cells(y, 11) = ws.Cells(36, 2)
ThisWorkbook.Sheets(1).Cells(y, 12) = ws.Cells(38, 2)
ThisWorkbook.Sheets(1).Cells(y, 13) = ws.Cells(40, 2)
ThisWorkbook.Sheets(1).Cells(y, 14) = ws.Cells(42, 2)
ThisWorkbook.Sheets(1).Cells(y, 15) = ws.Cells(46, 2)
ThisWorkbook.Sheets(1).Cells(y, 16) = ws.Cells(48, 2)
ThisWorkbook.Sheets(1).Cells(y, 17) = ws.Cells(50, 2)
ThisWorkbook.Sheets(1).Cells(y, 18) = ws.Cells(52, 2)
ThisWorkbook.Sheets(1).Cells(y, 19) = ws.Cells(55, 2)
ThisWorkbook.Sheets(1).Cells(y, 20) = ws.Cells(57, 2)
ThisWorkbook.Sheets(1).Cells(y, 21) = ws.Cells(59, 2)
ThisWorkbook.Sheets(1).Cells(y, 22) = ws.Cells(55, 5)
ThisWorkbook.Sheets(1).Cells(y, 23) = ws.Cells(57, 5)
ThisWorkbook.Sheets(1).Cells(y, 24) = ws.Cells(59, 5)
ThisWorkbook.Sheets(1).Cells(y, 25) = ws.Cells(62, 2)
ThisWorkbook.Sheets(1).Cells(y, 26) = ws.Cells(64, 2)
ThisWorkbook.Sheets(1).Cells(y, 27) = ws.Cells(66, 2)
ThisWorkbook.Sheets(1).Cells(y, 28) = ws.Cells(62, 5)
ThisWorkbook.Sheets(1).Cells(y, 29) = ws.Cells(64, 5)
ThisWorkbook.Sheets(1).Cells(y, 30) = ws.Cells(66, 5)
y = y + 1
Next ws
wb.Close
End If
Next wbFile
z = Sheets(1).Cells(4, 1).End(xlDown).Row
Sheets(1).Range("A5:AD" & z & "").Sort Key1:=Sheets(1).Range("A5"), Order1:=xlAscending, Header:=xlNo, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
End Sub