Macro anpassen sodass er alle Daten aus allen Arbeitsblättern kopiert

Sub Collect_Raw_Data_Test()
Dim Data_object As String
Dim Data_Char As String
Dim ITSys_Planned As String
Dim ITSys_Current As String
Dim Brand As String
Dim Max_Zeilen_Data_Object As Integer
Dim aktuelle_Zeile As Integer
Dim Max_Zeilen_ITSys_Current As Integer
Dim Max_Zeilen_ITSys_Planned As Integer
Dim Brand_counter As Integer
Dim Erste_Zeile_PRP As Long
Dim Spalte_Brand_Previous As Long
Dim Spalte_Current_IT_Sys_Previous As Long
Dim Spalte_Planned_IT_Sys_Previous As Long
Dim Spalte_Data_Object As Long
Dim Spalte_Current_IT_Sys_Succ As Long
Dim Spalte_Planned_IT_Sys_Succ As Long
Dim Spalte_Brand_Succ As Long
'Dim Erste_Zeile_RD As Long
'Dim Spalte_Brands_RD As Long
For i = 3 To 5
Sheets(i).Select
'Tue irgendetwas
Next i
aktuelle_Zeile = 0
Brand_counter = 0
ITSys_Planned_counter = 0
With ActiveWorkbook.Worksheets("PRP_3a") 'Ab hier werden dei Inhalte definiert die das PRP_x  _
ausmachen, bzw wo die Daten stehen.
'Dim meine_Blaetter()
'Dim blattindex As Long
'meine_Blaetter = Array("PRP_3a", "PRP_4a")  'hier erweitern aber wie?
'For blattindex = 0 To UBound(meine_Blaetter)
'With ActiveWorkbook.Worksheets(meine_Blaetter(blattindex))
Max_Zeilen_Data_Object = .Range("No_Data_Objects").Value
Max_Zeilen_ITSys_Current = .Range("No_IT_Sys_Prev").Value
Erste_Zeile_PRP = .Range("Beginn_Brands_Previous").Row + 1 'Zeile 47
Spalte_Brand_Previous = .Range("Beginn_Brands_Previous").Column 'Spalte 3
Spalte_Current_IT_Sys_Previous = .Range("Beginn_Current_IT_Sys_Previous").Column
Spalte_Planned_IT_Sys_Previous = .Range("Beginn_Planned_IT_Sys_Previous").Column
Spalte_Data_Object = .Range("Beginn_Data_Object").Column
Spalte_Current_IT_Sys_Succ = .Range("Beginn_Current_IT_Sys_Succ").Column
Spalte_Planned_IT_Sys_Succ = .Range("Beginn_Planned_IT_Sys_Succ").Column
Spalte_Brand_Succ = .Range("Beginn_Brands_Succ").Column
'Erste_Zeile_RD = .Range("PRP_RD").Row + 1
'Spalte_Brands_RD = .Range("Brands_RD").Column
'Spalte_Current_IT_Sys_RD = .Range("Current_IT_Sys_RD").Column
'Spalte_Current_Planned_IT_Sys_RD = .Range("Planned_IT_Sys_RD").Column
'Spalte_Current_Data_Objects_RD = .Range("Data_Objects_RD").Column
For j = 0 To Max_Zeilen_ITSys_Current - 1
For i = 0 To Max_Zeilen_Data_Object - 1
Brand = .Cells(Erste_Zeile_PRP + Brand_counter, Spalte_Brand_Previous).Value
ITSys_Current = .Cells(Erste_Zeile_PRP + j, Spalte_Current_IT_Sys_Previous).Value
ITSys_Planned = .Cells(Erste_Zeile_PRP + j, Spalte_Planned_IT_Sys_Previous).Value
Data_object = .Cells(Erste_Zeile_PRP + i, Spalte_Data_Object).Value
Worksheets("RawData").Cells(3 + aktuelle_Zeile, 3).Value = Brand
Worksheets("RawData").Cells(3 + aktuelle_Zeile, 4).Value = ITSys_Current
Worksheets("RawData").Cells(3 + aktuelle_Zeile, 5).Value = ITSys_Planned
Worksheets("RawData").Cells(3 + aktuelle_Zeile, 6).Value = Data_object
Worksheets("RawData").Cells(3 + aktuelle_Zeile, 7).Value = "Previous"
aktuelle_Zeile = aktuelle_Zeile + 1
Next i
Brand_counter = Brand_counter + 1
Next j
Max_Zeilen_ITSys_Current = .Range("No_of_IT_Sys_Plan")
For m = 0 To Max_Zeilen_ITSys_Current - 1
For p = 0 To Max_Zeilen_Data_Object - 1
Brand = .Cells(Erste_Zeile_PRP + m, 16).Value
ITSys_Current_succ = .Cells(Erste_Zeile_PRP + m, Spalte_Current_IT_Sys_Succ).Value
ITSys_Planned_succ = .Cells(Erste_Zeile_PRP + m, Spalte_Planned_IT_Sys_Succ).Value
Data_object = .Cells(Erste_Zeile_PRP + p, Spalte_Data_Object).Value
Worksheets("RawData").Cells(3 + aktuelle_Zeile, 3).Value = Brand
Worksheets("RawData").Cells(3 + aktuelle_Zeile, 4).Value = ITSys_Current_succ
Worksheets("RawData").Cells(3 + aktuelle_Zeile, 5).Value = ITSys_Planned_succ
Worksheets("RawData").Cells(3 + aktuelle_Zeile, 6).Value = Data_object
Worksheets("RawData").Cells(3 + aktuelle_Zeile, 7).Value = "Succeeding"
aktuelle_Zeile = aktuelle_Zeile + 1
Next p
Brand_counter = Brand_counter + 1
Next m
End With
End Sub