Finding the highest numbered worksheet within a booklet

[Red over White]

I have try to research the answer to the above issue and came across the following feed from 2015 - Find sheet that contains the highest number using VBA
The original poster the vba provided largely works.

Having input some of the code

Sub Macro()

Dim lngMyNumber As Long
Dim wsMysheet As Worksheet
Dim wsHighestSheet As Worksheet

For Each wsMysheet In ThisWorkbook.Sheets
If lngMyNumber = 0 Then
If RetNumerics(CStr(wsMysheet.Name)) > 0 Then
lngMyNumber = RetNumerics(CStr(wsMysheet.Name))
Set wsHighestSheet = wsMysheet
End If
Else
If Val(RetNumerics(CStr(wsMysheet.Name))) > lngMyNumber Then
lngMyNumber = RetNumerics(CStr(wsMysheet.Name))
Set wsHighestSheet = wsMysheet
End If
End If
Next wsMysheet

End Sub

However, I am getting "Compile error: Sub or Function not defined" on RetNumerics. Can anyone provide a solution and also say if I'm going to hit an issue with 'CStr' once past the initial hurdle.

Thanks

 

[jimrward]

You need to go back to the post you refer to and copy the retnumerics function that is below the code and include it after your code

 

[Red over White]

Thanks jimrward

This is first time I have used a function element so hopefully you will bear with me. If it helps the original inquiry said the worksheets were called Session1, Session2 and so on. I don't have that. I have a combination of sheets with numbers 2019, 2020 to 2025 with other worksheets that are wholly alpha.

My code now looks like this, but I'm still encountering an issue

Sub Macro()

Dim lngMyNumber As Long
Dim wsMysheet As Worksheet
Dim wsHighestSheet As Worksheet

For Each wsMysheet In ThisWorkbook.Sheets
If lngMyNumber = 0 Then
If RetNumerics(CStr(wsMysheet.Name)) > 0 Then
lngMyNumber = RetNumerics(CStr(wsMysheet.Name))
Set wsHighestSheet = wsMysheet
End If
Else
If Val(RetNumerics(CStr(wsMysheet.Name))) > lngMyNumber Then
lngMyNumber = RetNumerics(CStr(wsMysheet.Name))
Set wsHighestSheet = wsMysheet
End If
End If
Next wsMysheet

End Sub

Function RetNumerics(ByVal strMyText As String) As Variant

Static objREX As Object

If objREX Is Nothing Then Set objREX = CreateObject("VBScript.RegExp") ' Run time error '429' ActiveX component can't create object

With objREX
.Global = True
.Pattern = "[0-9]"
If .test(strMyText) Then
.Pattern = "[0-9]"
RetNumerics = CLng(.Replace(strMyText, vbNullString))
Else
RetNumerics = 0
End If
End With

Set objREX = Nothing

End Function