KentKHI
Active Member
- Joined
- Oct 1, 2004
- Messages
- 492
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:
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!
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
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!