Counting and Greater than actions??

[KentKHI]

I have an issue where I am naming sheets in code with a prefix such as A or CO or OPT, and a suffix that is a number. Currently it automatically assigns a number using the following code:

    ShCount = 0
    Duh = UserForm1.ComboBox1.Value
    Criterion = Duh + "*"
    If Criterion = "" Then Exit Sub
    For Each sh In Sheets
    If sh.Name Like Criterion Then ShCount = ShCount + 1
    Next sh

where Shcount is an integer, and sh is an object, sheet.

The problem is if I delete a sheet in the middle of a group (example: A1 A2 A3 A5 A6 A7) I get an error because I am trying to name a sheet the same as an existing sheet.

Is there a way to find the highest integer and +1 in order to not name sheets the same name? (result should be: A8)

(I know there is a way, but how?)

Also, or otherwise, even better, is there a way to name it using the first missing positive integer? (result should be: A4)

Thanks for any help on this!

 

[KentKHI]

The problem is if I delete a sheet in the middle of a group (example: A1 A2 A3 A5 A6 A7) I get an error because I am trying to name a sheet the same as an existing sheet.

Oops
I get the error when I add a new sheet!