Need to make more than 10 sheets

[justvba]

I am running this code in another set of code. I am tring to check for all the sheets that have the same name and then see what the last number is and add 1 to the sheet number. but when it gets to sheet number 10 it wont add 1 to mane it "sheet name - 11" how do I fix this..


Const BASE_NAME As String = "Shirt - Color Code "
Dim sheet_name As String
Dim i As Integer
Dim num_text As String
Dim new_num As String
Dim max_num As String
Dim new_sheet As Worksheet

Do Until IsEmpty(ActiveCell)
ans = Worksheets(Worksheets.Count).Range("A5").Value

If InStr(ActiveCell, ans) Then
Major = Left(ActiveCell.Value, InStr(ActiveCell.Value, "/") - 1)
'MsgBox "Match" Testing to see it the 2 matched or not
ElseIf ans = "" Then
Else
max_num = 0
For i = 1 To Sheets.Count
sheet_name = Sheets(i).Name
If Left$(sheet_name, Len(BASE_NAME)) = BASE_NAME _
Then
num_text = Mid$(sheet_name, Len(BASE_NAME) + 1)
new_num = Val(num_text)
If new_num > max_num Then max_num = new_num
End If
Next i
Set new_sheet = Sheets.Add(after:=Sheets(Sheets.Count))
new_sheet.Name = BASE_NAME & Format$(max_num + 1)
new_sheet.Select

End If

 

[Joe4]

Welcome to the Board!

You have declared max_num to be a string:

[COLOR=#333333]Dim max_num As String[/COLOR]

When you try to "add" to a string, it concatenates instead of adds.

You can try changing this line:

[COLOR=#333333]new_sheet.Name = BASE_NAME & Format$(max_num + 1)[/COLOR]

to

[COLOR=#333333]new_sheet.Name = BASE_NAME & Format$(Val(max_num) + 1)[/COLOR]

But I would be more inclined to go back in your code and declare all your number variables as numbers and not strings, and make any necessary adjustments.

 

[justvba]

perfect!! I did change the declared ver. to Integers and added the Val and it works great thank you!!!

 

[Joe4]

You are welcome!

If you change them all to Integers, I don't think you would need to use Val at all (this converts strings to numbers, but declaring the variables as numbers instead of string should already take care of this).
It doesn't hurt, but I don't think it would be necessary any more.

 

[justvba]

oh OK I will remove the VAL I misunderstood I thought I needed both. I am new to VBA so still learning every day.