Code schreiben

Sub neuesblatt()
'Erstellen von neuem Tabellenblatt und Einfügen der Zählenwenn-Formeln
'Aufbereiten der Übersicht und Erstellen der Verknüpfungen
Dim ws As Worksheet
Dim s As String
s = InputBox("Wie soll das neue Blatt heissen?", _
"Blattnamen vergeben", "Blattname?")
If s = "" Then
Exit Sub
Else
Sheets.Add
Set ws = ActiveSheet
ws.Name = s
Range("A1").FormulaR1C1 = "=TODAY()"
Range("A2").FormulaR1C1 = "=COUNTIF(R[5]C:R[998]C,""Bugsin2"")"
Range("B2").FormulaR1C1 = "=COUNTIF(R[5]C[-1]:R[998]C[-1],""J*"")"
Range("C2").FormulaR1C1 = "=COUNTIF(R[5]C[-2]:R[998]C[-2],""R*"")"
Range("D2").FormulaR1C1 = "=COUNTIF(R[5]C[-3]:R[998]C[-3],""TO*"")"
Range("E2").FormulaR1C1 = "=COUNTIF(R[5]C[-4]:R[998]C[-4],""ASRS"")"
Range("F2").FormulaR1C1 = "=COUNTIF(R[5]C[-5]:R[998]C[-5],""000*"")"
Range("G2").FormulaR1C1 = "=COUNTIF(R[5]C[-6]:R[998]C[-6],""RF*"")"
Range("A3").FormulaR1C1 = "=COUNTIF(R[4]C[4]:R[997]C[4],""Bugsin2"")"
Range("B3").FormulaR1C1 = "=COUNTIF(R[4]C[3]:R[997]C[3],""J*"")"
Range("C3").FormulaR1C1 = "=COUNTIF(R[4]C[2]:R[997]C[2],""R*"")"
Range("D3").FormulaR1C1 = "=COUNTIF(R[4]C[1]:R[997]C[1],""TO*"")"
Range("E3").FormulaR1C1 = "=COUNTIF(R[4]C:R[997]C,""ASRS"")"
Range("F3").FormulaR1C1 = "=COUNTIF(R[4]C[-1]:R[997]C[-1],""000*"")"
Range("G3").FormulaR1C1 = "=COUNTIF(R[4]C[-2]:R[997]C[-2],""RF*"")"
Range("A4").Value = "OS-Status"
Range("E4").Value = "RS-Status"
End If
Sheets("Übersicht").Select
Range("a8").Select
Do Until ActiveCell.Formula = ""
ActiveCell.Offset(1, 0).Select
Loop
ActiveCell.Value = s
ActiveCell.Offset(0, 1).Select
ActiveCell.Value = "OS"
ActiveCell.Offset(0, 1).Select
ActiveCell.FormulaR1C1 = "=test!R[-12]C[-2]" 'hier will ich mich auf das
'gerade erstellte Blatt beziehen.
End Sub