VBA: E xcel - Find sheet if exists and activate

[britur]

Hi,

Trying to create a macro to find a sheet (SheetName - dd/mm/yyyy) closest to todays date.

Code below only finds the sheet name within the quotes and not the format date.

Sub Test()
Dim sh As Worksheet, flg As Boolean
Dim i As Integer
Dim lookUpSheet As String
i = 0

For Each sh In Worksheets
lookUpSheet = "SheetName - " + Format(Date - i, "dd mmm yyyy")
If sh.name = lookUpSheet Then flg = True: Exit For
i = i + 1
Next
If flg = True Then
MsgBox "Found! " & lookUpSheet
Else
MsgBox "unable to find " & lookUpSheet
End If
End Sub

I would also like to be able to select the sheet once the loop finds it.
so, if sheet is named 'SheetName - 01 Oct 2012' and it is run today it should find and select that sheet that is -2 days from today.

can anyone help please?

 

[PHV]

A starting point:

...
i = -1
Do Until flg
  i = i + 1
  lookUpSheet = "SheetName - " & Format(Date - i, "dd mmm yyyy")
  For Each sh In Worksheets
    If sh.name = lookUpSheet Then flg = True: sh.Activate: Exit For
  Next
Loop
...

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[britur]

thats brill thank you

 

[DaveInIowa]

Another option and it will only loop through the Worksheets collection one time.

Public Sub Test()
    Dim ws As Worksheet
    Dim dt As Date
    Dim minDaysFromToday As Integer
    Dim wsClosest As Worksheet
    
    minDaysFromToday = 32767
    Set wsClosest = Nothing

    For Each ws In Worksheets
        If (ws.Name Like "SheetName - ## ??? ####") Then
            If (IsDate(Right(ws.Name, 11))) Then
                dt = CDate(Right(ws.Name, 11))
                If (dt <= Date And (Date - dt) < minDaysFromToday) Then
                    minDaysFromToday = Date - dt
                    Set wsClosest = ws
                End If
            End If
        End If
    Next
    
    If (wsClosest Is Nothing) Then
        MsgBox "Unable to find any sheets like 'SheetName - ## ??? ####'"
    Else
        wsClosest.Activate
        MsgBox "Closest sheet to today's date is '" & wsClosest.Name & "'"
    End If

End Sub