Add number sequentially to string based on formula

I have the following macro that runs beautifully that replicates a sheet in my workbook and removes rows from the original sheet based on certain criteria.
I wish to amend this code so that the resulting string that remains in cells A17:A190 is effected by the following formula:

If A18 is blank then leave as blank

=IF(MID(A18,8,1)="/" then left(a18,8)+mid(a18,9,3) +1
so if there is P009959/004 in A18 the macro places P009959/005 in its place.

=IF(LEFT(A18,1)="S" then left(a18,10)+mid(a18,10,3)+1
so if there is SP0091377/001A in A18 the macro places SP0091377/002 in its place

otherwise
LEFT(A18,10)


here is my macro so far

Sub SheetMaker()
Dim NewSheet As Worksheet, NewName As String
Dim shName As Variant, v As Variant

shName = Application.InputBox(Prompt:="Enter sheet name:", Type:=2)
If shName = False Then Exit Sub

For Each s In Sheets
    If s.Name = shName Then
        GoTo GoodToGo
        Else
        MsgBox "Sheet Doesnt Exist"
        Exit Sub
        
    End If
Next
Exit Sub
GoodToGo:
ary = Split(shName, " ")
ary(1) = ary(1) + 1
NewName = Join(ary, " ")
Sheets(shName).Copy after:=Sheets(Sheets.Count)
ActiveSheet.Name = NewName

For l = 190 To 17 Step -1
    v = Cells(l, "I").Value
    If v = "NTU" Or v = "Declined" Or v = "Non-Renewed" Or v = "Extended" Then
        Cells(l, "I").EntireRow.Clear
    End If
Next
End Sub



copytest.xls