Code:
Sub neueZeile()
For Each Sheet In Sheets
If Sheet.Name = "Vorschlag" Then
Else
Vs = Vs + 1
End If
Next
If Vs = Sheets.Count Then
Sheets.Add
ActiveSheet.Name = "Vorschlag"
ActiveSheet.Range("A1") = "Anlage"
ActiveSheet.Range("B1") = "Qualifikation"
ActiveSheet.Range("C1") = "Mitarbeiter"
End If
l1 = Sheets("Ausgangsdatei").Cells(Rows.Count, 1).End(xlUp).Row
l2 = Sheets("Vorschlag").Cells(Rows.Count, 1).End(xlUp).Row + 1
Sheets("Vorschlag").Range("A2:C" & l2).Clear
For i = 2 To l1
l1 = Sheets("Ausgangsdatei").Cells(Rows.Count, 1).End(xlUp).Row
l2 = Sheets("Vorschlag").Cells(Rows.Count, 1).End(xlUp).Row + 1
With Sheets("Vorschlag")
If Sheets("Ausgangsdatei").Cells(i, 3) > "" Then
.Cells(l2, 1) = Sheets("Ausgangsdatei").Cells(i, 1)
.Cells(l2, 1).HorizontalAlignment = xlCenter
.Cells(l2, 2) = Sheets("Ausgangsdatei").Cells(i, 2)
.Cells(l2, 2).HorizontalAlignment = xlCenter
.Cells(l2, 3) = Sheets("Ausgangsdatei").Cells(i, 3)
.Cells(l2, 3).HorizontalAlignment = xlCenter
End If
If Sheets("Ausgangsdatei").Cells(i, 4) > "" Then
.Cells(l2 + 1, 1) = Sheets("Ausgangsdatei").Cells(i, 1)
.Cells(l2 + 1, 1).HorizontalAlignment = xlCenter
.Cells(l2 + 1, 2) = Sheets("Ausgangsdatei").Cells(i, 2)
.Cells(l2 + 1, 2).HorizontalAlignment = xlCenter
.Cells(l2 + 1, 3) = Sheets("Ausgangsdatei").Cells(i, 4)
.Cells(l2 + 1, 3).HorizontalAlignment = xlCenter
End If
If Sheets("Ausgangsdatei").Cells(i, 5) > "" Then
.Cells(l2 + 2, 1) = Sheets("Ausgangsdatei").Cells(i, 1)
.Cells(l2 + 2, 1).HorizontalAlignment = xlCenter
.Cells(l2 + 2, 2) = Sheets("Ausgangsdatei").Cells(i, 2)
.Cells(l2 + 2, 2).HorizontalAlignment = xlCenter
.Cells(l2 + 2, 3) = Sheets("Ausgangsdatei").Cells(i, 5)
.Cells(l2 + 2, 3).HorizontalAlignment = xlCenter
End If
If Sheets("Ausgangsdatei").Cells(i, 6) > "" Then
.Cells(l2 + 3, 1) = Sheets("Ausgangsdatei").Cells(i, 1)
.Cells(l2 + 3, 1).HorizontalAlignment = xlCenter
.Cells(l2 + 3, 2) = Sheets("Ausgangsdatei").Cells(i, 2)
.Cells(l2 + 3, 2).HorizontalAlignment = xlCenter
.Cells(l2 + 3, 3) = Sheets("Ausgangsdatei").Cells(i, 6)
.Cells(l2 + 3, 3).HorizontalAlignment = xlCenter
End If
If Sheets("Ausgangsdatei").Cells(i, 7) > "" Then
.Cells(l2 + 4, 1) = Sheets("Ausgangsdatei").Cells(i, 1)
.Cells(l2 + 4, 1).HorizontalAlignment = xlCenter
.Cells(l2 + 4, 2) = Sheets("Ausgangsdatei").Cells(i, 2)
.Cells(l2 + 4, 2).HorizontalAlignment = xlCenter
.Cells(l2 + 4, 3) = Sheets("Ausgangsdatei").Cells(i, 7)
.Cells(l2 + 4, 3).HorizontalAlignment = xlCenter
End If
End With
Next
End Sub