Find latest copy of Worksheet eg Worksheet(N)

Question

I have a workbook containing multiple worksheets.

The sheets are named in date format dd ddd mmm. eg Wed 15 Aug. There could be many sheets named the same day but with a simple (N) at the end eg. Wed 15 Aug (3). these have been copied by worksheet.count at an early stage.

I have a Range("F3") used to find the sheet I am looking for. However, I can't seem to correctly figure out how to find the last sheet in its named series. eg Wed 12 Aug (3).

The below function and relating sub works... but only finds the first in the named series eg Wed 12 Aug. I need help modifying this to find the the last in the series eg Wed 12 Aug (3). The workbook contains many different days of data.

Any help is very much appreciated.

Function SheetExist(strSheetName As String) As Boolean
    Dim i As Integer

    For i = 1 To Worksheets.Count
        If Worksheets(i).Name = strSheetName Then
            SheetExist = True
            Exit Function
        End If
    Next i
End Function


Sub FindlastestUpdate()

    Application.DisplayAlerts = False
    Application.ScreenUpdating = False

    Dim Dfind As String 
    Dfind =  Workbooks("Inbound.Control.xlsm").Worksheets("ControlPanel").Range("F3")
    Dfind = Format(Dfind, "ddd dd mmm")

    If SheetExist(Dfind) Then
        Workbooks("Inbound.Control.xlsm").Worksheets(Dfind).Select

        Debug.Print "The Sheet Exists"

    Else 'What Happends if the sheet dosnt exist

        Debug.Print "The Sheet Does NOT Exists"

    End If
End Sub

Answer 1

Here is an example of how looping and using Split might look. Change the workbook references as required. Here the F3 value is looked for in each worksheet name using Instr. If present then the code looks to see if the name has "(" present. If it does it splits on this, and on ")", to extract the number present in copies of a sheet. This could benefit from some error handling. The number extracted is compared to a finalNumber variable. If it is greater then the finalNumber is set to the extracted number, and so on. If "(" is not found then the final sheet required is assumed to be the sheet found, without "(" in it, but that was matched on the F3 value.

Option Explicit
Public Sub test()
    Dim Dfind As String, ws As Worksheet, number As Long, finalNumber As Long, lastSheet As Worksheet
    Dfind = vbNullString

    Dfind = Workbooks("Inbound.Control.xlsm").Worksheets("ControlPanel").Range("F3")
    Dfind = Format$(Dfind, "ddd dd mmm")

    For Each ws In ThisWorkbook.Worksheets '<==change as appropriate
        If InStr(ws.Name, Dfind) > 0 Then
            If InStr(ws.Name, "(") Then
                number = Split(Split(ws.Name, "(")(1), ")")(0)
                If number > finalNumber Then
                    finalNumber = number
                    Set lastSheet = ws
                End If
            Else
                Set lastSheet = ws
            End If
        End If
    Next
    If Not lastSheet Is Nothing Then Debug.Print lastSheet.Name
End Sub