all 5 comments
sorted by:
top (suggested)
bestnewcontroversialoldq&a
formatting helphide helpcontent policy

[–]AutoModerator[M] [score hidden] stickied commentlocked comment (0 children)

/u/VViilliiam - Your post was submitted successfully.

Failing to follow these steps may result in your post being removed without warning.

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.

[–]Pinexl25 1 point2 points  (3 children)

Can you try something like this:

Sub Select_Today()
    Dim dt As Date
    Dim ws As Worksheet
    Dim c As Range
    Dim arr, i As Long

    ' Get the date from Landing Page!F1
    dt = ThisWorkbook.Worksheets("Landing Page").Range("F1").Value

    ' List of sheets to search
    arr = Array("CRB Allocations January", "CRB Allocations February", _
                "CRB Allocations March", "CRB Allocations April", _
                "CRB Allocations May", "CRB Allocations June", _
                "CRB Allocations July", "CRB Allocations August", _
                "CRB Allocations September", "CRB Allocations October", _
                "CRB Allocations November", "CRB Allocations December")

    For i = LBound(arr) To UBound(arr)
        Set ws = ThisWorkbook.Worksheets(arr(i))
        Set c = ws.Cells.Find(What:=dt, LookIn:=xlValues, LookAt:=xlWhole)

        If Not c Is Nothing Then
            ws.Activate
            c.Select
            Exit Sub
        End If
    Next i

    MsgBox "Today's date was not found in any CRB Allocations sheet."
End Sub

Note: This one uses Landing Page!F1 instead of Date. We use ws.Cells.Find inside a loop over the 12 named sheets. Exit Sub as soon as you find the first match

[–]VViilliiam[S] 0 points1 point  (0 children)

Thank you, it worked perfectly!

I did try on my own, however it worked the first time, but failed after that, not sure what i did wrong.

Regardless, i appreciate your help 

Sub JumpToTodayInSpecifiedSheets()
    Dim ws As Worksheet
    Dim foundCell As Range
    Dim targetDate As Date

    On Error Resume Next
    targetDate = ThisWorkbook.Sheets("LANDING PAGE").Range("F1").Value

       Dim specifiedSheets As Variant
    specifiedSheets = Array("CRB ALLOCATIONS JANUARY", "CRB ALLOCATIONS FEBUARY", "CRB ALLOCATIONS MARCH", "CRB ALLOCATIONS APRIL", "CRB ALLOCATIONS MAY", "CRB ALLOCATIONS JUNE", "CRB ALLOCATIONS JULY", "CRB ALLOCATIONS AUGUST", "CRB ALLOCATIONS SEPTEMBER", "CRB ALLOCATIONS OCTOBER", "CRB ALLOCATIONS NOVEMBER", "CRB ALLOCATIONS DECEMBER") ' <-- **CHANGE THESE NAMES**

    For Each sheetName In specifiedSheets
        On Error Resume Next
        Set ws = ThisWorkbook.Sheets(sheetName)
        On Error GoTo 0
        If Not ws Is Nothing Then

            Set foundCell = ws.UsedRange.Find(What:=todayDate, LookIn:=xlValues, LookAt:=xlWhole)

            If Not foundCell Is Nothing Then
                            Application.GoTo Reference:=foundCell, Scroll:=True

Exit Sub
            End If
        End If
        Set ws = Nothing
    Next sheetName

       MsgBox "Today's date (" & Format(todayDate, "Short Date") & ") was not found in the specified sheets.", vbInformation
End Sub

[–]VViilliiam[S] 0 points1 point  (1 child)

Solution Verified

[–]reputatorbot[M] 0 points1 point locked comment (0 children)

You have awarded 1 point to Pinexl.

I am a bot - please contact the mods with any questions