AW: Spalten in Zeilen umwandeln
05.12.2023 16:04:33
UweD
Hallo
versuch das mal
Private Sub Olaf()
Dim TB1 As Worksheet, TB2 As Worksheet, i As Long
Dim Sp1 As Integer, Sp2 As Integer, ZE As Integer, LR1 As Long, LC1 As Integer, LR2 As Long
'*** bescheunigt das Makro
Application.ScreenUpdating = False
'*** Stammdaten Anfang
Set TB1 = Sheets("Tabelle1")
Set TB2 = Sheets("Tabelle2")
Sp1 = 1 'Spalte A
Sp2 = 5 'Spalte E
ZE = 5 'ab Zeile
'*** Stammdaten Ende
With TB2
LR1 = TB1.Cells(TB1.Rows.Count, Sp1).End(xlUp).Row 'letzte Zeile der Spalte
LC1 = TB1.Cells(ZE, TB1.Columns.Count).End(xlToLeft).Column 'letzte Spalte einer Zeile
LR2 = .Cells(.Rows.Count, Sp1).End(xlUp).Row
'Reset
.Rows(2).Resize(LR2).Delete
For i = ZE + 1 To LR1
LR2 = .Cells(.Rows.Count, Sp1).End(xlUp).Row + 1
.Cells(LR2, 1).Resize(LC1 - Sp2 + 1, 1) = TB1.Cells(i, 1)
.Cells(LR2, 2).Resize(LC1 - Sp2 + 1, 1) = Application.Transpose(TB1.Cells(ZE, Sp2).Resize(1, LC1 - Sp2 + 1))
.Cells(LR2, 4).Resize(LC1 - Sp2 + 1, 1) = Application.Transpose(TB1.Cells(i, Sp2).Resize(1, LC1 - Sp2 + 1))
.Cells(LR2, 7).Resize(LC1 - Sp2 + 1, 1) = TB1.Range("B2")
.Cells(LR2, 8).Resize(LC1 - Sp2 + 1, 1) = TB1.Range("B3")
Next i
End With
End Sub
LG UweD