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 cant 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

Gather all the qualifying sheets based on date part. Then loop those splitting on "(" and on ")" (with Split function taking sheet.Name input) and find the largest number. That will be the sheet you want. — QHarr

QHarr QHarr · Accepted Answer · 2018-08-19T16:51:00

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

Find latest copy of Worksheet eg Worksheet(N)

1 Answers