Excel VBA find newest worksheet based on date/time stamp in worksheet name?

**takchin.tc** · 10-26-2012, 11:57 AM

Excel VBA find newest worksheet based on date/time stamp
in worksheet name?

I have 4 worksheets (will have many more in the future) with the following
date/time stamps as part of the worksheet name:

INV2012_08_02_1018 AM
INV2012_09_17_2218 PM
INV2012_10_20_0618 AM
INV2012_10_23_0118 PM

How can I use VBA to search for and activate the newest
worksheet based on the date/time stamp in the worksheet name?

This is only for the worksheet names that start with "INV"
and have a date/time stamps in the worksheet name.

Correct answer: INV2012_10_23_0118 PM

Thank You, in advance.

**patel45** · 10-26-2012, 01:00 PM

It's very easy if the last in time is in the last position

**takchin.tc** · 10-27-2012, 09:48 AM

...and if the worksheet is not in the last position because other worksheets were created?

I'm looking for a VBA solution tha actually reads the worksheet name and compares it to all the other worksheet names.

Dim wkb As Workbook

    Dim LResult As String
    Dim RResult As String

    LResult = Left(wkb.Name,3)
    RResult = Right(wkb.Name,18)
    
    For Each wkb In Workbooks
        If  LResult = "INV" Then

  [code to compare RResult to find most recent]

            Exit For
        End If
     Next wkb

Thanks.

**event21** · 10-27-2012, 10:26 AM

Hi -

You can start from this;

Sub test()
Dim oldDate As Date, ndx As Integer
oldDate = "1/1/1970"
ndx = 1
For Each sht In Worksheets
    If InStr(sht.Name, "INV") > 0 Then
        txt = Replace(sht.Name, "INV", vbNullString)
        y = Left(txt, 4): m = Mid(txt, 6, 2): d = Mid(txt, 9, 2): t1 = Mid(txt, 12, 2): t2 = Right(txt, 5)
        If Format(m & "/" & d & "/" & y & " " & t1 & ":" & t2, "m/d/yyyy hh:mm AM/PM") > oldDate Then
            oldDate = Format(m & "/" & d & "/" & y & " " & t1 & ":" & t2, "m/d/yyyy hh:mm AM/PM")
            ndx = sht.Index
        End If
    End If
Next
Sheets(ndx).Select
End Sub

Regards,
event

**Cutter** · 10-27-2012, 03:56 PM

@ takchin.tc

Welcome to the forum.

Please notice that [CODE] tags have been added to your post(s). The forum rules require them so please keep that in mind and add them yourself whenever showing code in any of your future posts. To see instructions for applying them, click on the Forum Rules button at the top of the page and read Rule #3.
Thanks.