|
Hallo,
also ehrlich gesagt habe ich keine Ahnung was der Aufwand soll. Deine Nummerierung kannst du doch einfach über die Formel =ZEILE()-4 eintragen.
Private Sub CommandButton3_Click()
Dim i As Long, Zeile As Long
Dim Abteilung As String, Name As String
Dim LfdNr As Integer
Dim Blatt As Worksheet
For Each Blatt In ActiveWorkbook.Sheets
Blatt.Unprotect ("KdoSAN")
Next
Zeile = Application.InputBox("nach welcher Zeile einfuegen?", "Zeile einfuegen", 0, Type:=1)
If Zeile = 0 Then Exit Sub
Application.EnableEvents = False
Abteilung = Application.InputBox("Abtl.", "Abt. einfuegen", Type:=2)
Name = Application.InputBox("Name", "Name eintragen", Type:=2)
For i = ActiveSheet.Index To Worksheets.Count
With Worksheets(i)
.Rows(Zeile).Insert xlDown
.Cells(Zeile, 1).FormulaLocal = "=ZEILE()-4"
'oder halt
'.Cells(Zeile, 1).FormulaLocal = "=ZEILEN($A$5:A" & Zeile & ")"
.Cells(Zeile, 2) = Abteilung
.Cells(Zeile, 3) = Name
End With
Next
Application.EnableEvents = True
End Sub
Gruß Werner
|